CODEWITHSUNDEEP
sqljoinforeign-keysinner-joinforeign-key-relationship
milesg
milesg

Reputation: 3

Join 3 tables with different FKs

I'm trying to join 3 tables together in a SELECT query with some WHERE clauses. Table 1 is linked to Table 2, and Table 2 is linked to Table 3.

The tables are as follows:

Author
PK: Author_ID
FK: Location_ID
Author_First_Name

Location
PK: Location_ID
City

Articles
PK: Article_ID
FK: Author_ID
Article_Name

So far I've put together this Query, but am having trouble determining how to to Join the second Foreign Key 'Location_ID'

SELECT 
    Articles.Article_Name 
FROM 
    Articles
        INNER JOIN Author
            ON Articles.Author_ID
        INNER JOIN Location
            ON 
WHERE Author.Author_First_Name='Sam'
AND Location.City<>'Detroit'

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The proper syntax is something like this:

SELECT a.Article_Name
FROM Articles a INNER JOIN
     Author au 
     ON a.Author_ID = au.Author_ID INNER JOIN
     Location l
     ON l.Location_ID = au.Location_Id
WHERE au.First_Name = 'Sam' AND l.City <> 'Detroit';

Note the use of table aliases make the query easier to write and to read. Also, you need conditions connecting the two tables for the ON clauses.

Upvotes: 1

Jo&#227;o Pinho
Jo&#227;o Pinho

Reputation: 365

You forgot to compare table fields

SELECT * FROM articles ar
LEFT JOIN author a ON ar.author_id = a.author_id
LEFT JOIN location l ON a.location_id = l.location_id
WHERE a.author_first_name = 'Sam' AND l.city <> 'Detroit'

Upvotes: 0

Related Questions