milesg milesg - 2 months ago 8
SQL Question

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'

Answer

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.