Question How can I join 3 tables using SQL?

thugster69

Active member
Joined
Jun 17, 2010
Messages
35
Programming Experience
Beginner
Hey Guys,

Good day! I just want to learn how to join 3 tables in my SQL Database?

Here is the structure:

Table Name: Section1
Fields: Student Number


Table Name: New Student
Fields: Student Number
Last Name
First Name
Middle Name

Table Name: Old Student
Fields: Student Number
Last Name
First Name
Middle Name



How can I join records from [Old Student] and [New Student] tables by using the [Student Number] field in the Section1 table?

This is a representation of the desired result

VB.NET:
[COLOR="blue"]Student Number   |      Last Name      |   First Name    |  Middle Name[/COLOR]
1000                   Doe               John                  Kennedy              <=came from new student
2000                   Doe               Jane                  Maslow               <=came from old student
I've tried right, inner, outer join and even Union.. But still, to no avail.. Can you help me on this one mates?

Thank you very much!
 
Last edited:
Something like this would do it:

Select * From Section1 T1
Left Outer Join [New Student] T2
On T2.[Studunet Number] = T1.[Studunet Number]
Left OUter Join [Old Student] T3
On T3.[Studunet Number] = T1.[Studunet Number]

If you only wanted specific columns from each table you would then use T3.[Last Name] to pick up the Last Name field from the Old Student Table. I've also only used [] as there are spaces in the table and column names, you don't need them if it's all one word.

Hope that helps...
 
@Sultan

Thanks for the reply. I figured a way around my prob and here it is..

VB.NET:
SELECT Grade1mapagbigay.[Student Number],[Student Masterlist].[Last Name],
        [Student Masterlist].[First Name], [Student Masterlist].[Middle Name],[Student Masterlist].enrollmentState
        
FROM(Grade1Mapagbigay
INNER JOIN [Student Masterlist] ON Grade1Mapagbigay.[Student Number] = [Student Masterlist].[Student Number])

UNION

SELECT Grade1mapagbigay.[Student Number],[Temporary Enrollees].[Last Name],
        [Temporary Enrollees].[First Name], [Temporary Enrollees].[Middle Name], [Temporary Enrollees].enrollmentState
FROM(Grade1Mapagbigay
INNER JOIN [Temporary Enrollees] ON Grade1Mapagbigay.[Student Number] = [Temporary Enrollees].[Student Number])

The Tables are different since their in the actual implementation. But the concept is still the same.. Do you have any suggestion about the above posted code or something?
 
Back
Top