Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Alan Pietrzyk
3,787 PointsSQL - how to join multiple (3+) tables in a statement
Hello, for example, I'd like to retrieve data that exists in 4 tables. There are common identifiers in a couple of them between the four, but not across all four. I started doing this with an inner or left join, but am at a loss at how to add more than the 2 tables.
This is a sample of my join table and where if fails:
select * from Cost left join Item_Table on mmItem=ezItem ---connecting on Item # left join Stock_Table on mtItem=ezItem and mtAcct=ezAcct -- connecting on Item and Acct #
----below is were this query fails when I added another join, so I attempted to add this additional 'select'. Below, if the query stands alone, it works. But with the above it does not.
select * from DO_Table left join Stock_Table on mrtOrder=mtOrder and mtOrderLine=mrOrderLine
where ezdDivision='ABC' Order by ITEM#
===========================================
Initially my select statement was stitched like so below. But I suspect this is not ideal practice:
select Transaction_Date, Accting#, ITEM#, Division, Facility, Warehouse, Cost from DO_Table, Item_Table, Cost_Table, Stock_Table
where ezDivision='ABC' AND mtItem=mmitem -- item # found in 2 tables above AND mtAcct=ezAcct -- accounting # found in 2 tables above and mtOrder=mrOrder ---order lines found in 2 tables above and mtOrderLine=mrOrderLine -- order line # found in 2 tables above
Order by ITEM#
Any suggestions? Thanks, Alan
1 Answer
jcorum
71,830 PointsAssume you have one main table, Table1, and its primary key is Table1ID. You also have 3 other tables, Table2, Table3 and Table4, with primary keys (PKs) of Table2ID, Table3ID, and Table4ID. Further, the PKs of these 3 tables occur in Table1 as foreign keys (FKs). This would be the case, for example, if Tables 2, 3 and 4 were lookup tables for Table1.
For example, for a Faculty table the lookup tables might be Division, with DivisionID as the PK, Country, with CountryID as the PK, and Nationality, with NationalityID as the PK. To join Faculty to the Division, Country, and Nationality tables, the fields DivisionID, CountryID and NationalityID would need to be foreign keys in the Faculty table.
The SQL to join them would then be:
SELECT <fieldlist> FROM Faculty AS f
INNER JOIN Division AS d ON d.FacultyID = f.FacultyID
INNER JOIN Country AS c ON c.FacultyID = f.FacultyID
INNER JOIN Nationality AS n ON n.FacultyID = f.FacultyID
Depending on the tables and the needed response, the joins could be outer joins, left or right.