Welcome to the Treehouse Community
The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.
Alice Wein218 Points
Joining 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?
andi mitreTreehouse Guest Teacher
For 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.
code .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.