milesg milesg - 1 year ago 85
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download