Sometimes we need to fetch data from three or more tables. There are two types available to do these types of joins. Suppose we have three tables named Student, Marks, and Details.
Let's say Student has (stud_id, name) columns, Marks has (school_id, stud_id, scores) columns, and Details has (school_id, address, email) columns.
Using SQL Join Clause :
This approach is similar to the way we join two tables. The following query returns result from three tables :
SELECT name, scores, address, email FROM Student s
INNER JOIN Marks m on s.stud_id = m.stud_id
INNER JOIN Details d on d.school_id = m.school_id;
Using Parent-Child Relationship :
It is another approach to join more than two tables. In the above tables, we have to create a parent-child relationship. First, create column X as a primary key in one table and as a foreign key in another table. Therefore, stud_id is the primary key in the Student table and will be a foreign key in the Marks table. Next, school_id is the primary key in the Marks table and will be a foreign key in the Details table. The following query returns result from three tables :
SELECT name, scores, address, email
FROM Student s, Marks m, Details d
WHERE s.stud_id = m.stud_id AND m.school_id = d.school_id;
