How to use Sql join to include a specific column from another table

Question

I have two tables, author and book. Author contains the columns (isbn, name, city, state) and book contains (isbn, title, publisher, category, price, units)

For my assignment I need to output name, city and state (from the author table) and title (from the book table) only for authors from Exeter, NH, Palo Alto, CA, or Arlington, TX.

I can't get join part to work without an error popping up

SELECT 
    NAME,
    city,
    state
FROM 
    author

left JOIN book
    USING (title);

WHERE
    (city = 'exeter'AND state = 'NH') OR
    (city = 'arlington'AND state = 'TX') OR 
    (city ='palo alto' AND state = 'CA');

the tables were given in the assignment, is there a way to only pull the title column using join? — Jason MacMillan, Apr 04 '22 at 19:05
People check your previous questions... to see how responsive you are. When I answered your previous question [you never came back to me](https://stackoverflow.com/questions/66921753/fetch-execute-cycle-for-lmc-instructions). Why is that? — trincot, Apr 04 '22 at 19:45

score 2 · Accepted Answer · edited Apr 04 '22 at 19:29

What is your DBMS?

you need a field to relate the 2 tables, I suppose it is the ISBN that is the only one in common... (although as they already asked you why the ISBN is in the author?)...

SELECT a.NAME, b.title,a.city,a.state 
FROM author a 
LEFT JOIN book b 
    ON a.isbn = b.isbn 
WHERE (a.city = 'exeter'AND a.state = 'NH') 
    OR (a.city = 'arlington'AND a.state = 'TX')
    OR (a.city ='palo alto' AND a.state = 'CA')

score 0 · Answer 2 · answered Apr 04 '22 at 19:25

0

Left outer join should work so long as you join on the columns shared by the tables.

SELECT a.name
,a.city
,a.state
,b.title
FROM author a
LEFT OUTER JOIN book b on a.isbn = b.isbn
WHERE

answered Apr 04 '22 at 19:25

BobbyW

56
5

How to use Sql join to include a specific column from another table

2 Answers2