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
Alice Wein
218 PointsJoining multiple SQL tables
How do I join 4 or more tables, and do they need to have a key in common - either PK or Foreign?
2 Answers
andi mitre
Treehouse Guest TeacherFor a deep understanding of databases I would recommend the 'Database Foundations' course. But to answer specifically what you are looking for I would check out this video.
Say you want to join 4 tables:
1.student 2.test_results 3.grades 4.class_year
To be able to join the student table to test_results you would need to have either a student_id stored as FK in the test_results table or the test_id stored in the student table. Then you can continue to join as many tables as you'd like based on which tables contain relationships.
An example of the code would look like:
SELECT A.*, B.TEST_NAME, B.TEST_SCORE, C.CLASS_YEAR_NAME
FROM STUDENT A
INNER JOIN TEST_RESULTS B ON A.TEST_ID = B.TEST_ID
INNER JOIN CLASS_YEAR C ON A.YEAR_ID = C. YEAR_ID
WHERE A.FIRST_NAME = 'Alice'
This code selects everything from the student table along with test_name, test_score from the test_results table along with class_year_name from the class_year table by joining on test_id and year_id.
Cheers
Andy Demostene
6,091 Pointscode .am is correct. they don't all have to have the same key but every time you are joining one, it needs to have a common key with another table that already joined.