What is the proper method of using sql join with more than two tables?

Question

I have a tricky (to me, at least) challenge involving a query on 3 different tables to find the:

Study_ID, Study_Name, Price, Sdate on all students in grade.freshman (grade=freshman)

I started trying to piece it together, but I quickly became lost in the complexity. I feel like you need to join, but I can't puzzle it out.

This is as far as I got before getting lost.

SELECT Study_ID, Study_Name, Price, Sdate
     FROM Receives
          INNER JOIN Study ON Study_ID, Study_Name
          INNER JOIN Student ON Grade='freshmen';

Unfortunately, that is it. Here are the tables.

CREATE TABLE Student(
     S_ID char(6), 
     Name varchar(20), 
     Grade varchar(20), 
     Age integer, 
     PRIMARY KEY(S_ID)
);

CREATE TABLE Study(
     Study_ID varchar(6), 
     Study_Name varchar(20), 
     Label varchar(15), 
     PRIMARY KEY (Study_ID)
);

CREATE TABLE Receives(
     S_ID char(6), 
     Study_ID char(6), 
     Sdate date, 
     Price decimal(5,2), 
     PRIMARY KEY(S_ID, Study_ID), 
     FOREIGN KEY(Study_ID)  
     REFERENCES Study(Study_ID)
);

Note that `Student.Grade = "freshman"` is conceptually a selection/filter condition, rather than a join condition. — outis, Mar 25 '21 at 01:23
Does this answer your question? [SQL query return data from multiple tables](https://stackoverflow.com/questions/12475850/sql-query-return-data-from-multiple-tables) — outis, Apr 30 '21 at 23:56

outis · Accepted Answer · 2021-03-26T19:06:13.313

Here are two approaches: consider how the tables are related to each other, and consider the columns/foreign keys in the tables.

The Student and Study tables aren't directly related, but the Receives table is the relationship between the two (it's sometimes called an associative, junction, link or composite table, among other terms), so you can join Student and Study through Receives.

From the column/foreign key approach, the Receives table has columns that reference the Student and Study tables, so it can be used to join those tables (the definition of Receives is missing the Student(S_ID) foreign key, which becomes noticeable when you consider how the tables are related).

In SQL, it would look something like:

SELECT ...
  FROM Receives
    INNER JOIN Study ON ...
    INNER JOIN Student ON ...

The order of tables in the JOIN clauses is a matter of style; here, I give the joining table first. More typically, I list them as they are joined (which reads more like natural language). For example, Student ⋈ Receives ⋈ Study ("A student Receives study"), or (in SQL):

Student
  JOIN Receives ON ...
  JOIN Study ON ...

I changed my original attempt to reflect your help. Maybe something like that? Sorry, I'm not able to test it where I'm at right now. — linda baldwell, Mar 24 '21 at 23:54
@lindabaldwell: it's better not to change the original question, as it creates a new question and can invalidate answers. If you want clarifications in an answer, you can always ask in the comments. — outis, Mar 26 '21 at 19:07

What is the proper method of using sql join with more than two tables?

1 Answers1