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.

Development Tools Database Foundations Joining Relational Data Between Tables in SQL Joining Tables and Aliasing

Alice Wein
Alice Wein
218 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?

2 Answers

andi mitre
STAFF
andi mitre
Treehouse 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.

Cheers

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.