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

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');

CodePudding user response：

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')

CodePudding user response：

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