How to join multiple tabels in the query scripts

Question

I have 3 tabels in my SQL Server database with the following info:

tabel:      TeacherInfo
columns:     tID,     tName,     tAge,     tLocationId
values       1         Abel       53          1
             2         Bob        23          2
             3         Apaul      24          2

tabel:      LocationInfo
columns:    lId,    lDesc
values:     1     Street A
            2     Street B

tabel:      CourseInfo
columns:    cId,    cName,   masterTeacherId,   assistTeacherId
values:     1       Maths          1                    2
            2       English      1                    3

What I need is a SQL query script that can return the following values,

couseName,  masterName, masterAge,  masterLocation, assistName, assistAge,  assistLocation

Maths       Abel        53      Street A    Bob       23        Street B
English     Abel        53      Street A    Apaul       24      Street B

So how to write the SQL script? Thanks in advance!

Answer 1

select cName as couseName, a.tName as masterName, a.tAge as masterAge, 
a.lDesc as masterLocation, c.tName as assistName, c.tAge as assistAge, 
d.lDesc as assistLocation
from CourseInfo
join TeacherInfo a on masterTeacherId = a.tID
join LocationInfo b on a.tLocationId = b.lId
join TeacherInfo c on assistTeacherId = c.tID
join LocationInfo d on c.tLocationId = d.lId

Answer 2

You need to alais the tables differentially in the FROM clause:

SELECT CourseInfo.cName as courseName, Mst.tName as masterName, 
        Mst.tAge as masterAge,  MstLoc.lDesc as masterLocation, 
        Ass.tName as assistName, Ass.tAge as assistAge,  AssLoc.lDesc as 
assistLocation 

FROM CourseInfo 
    join TeacherInfo Mst on masterTeacherId = Mst.tID 
    join LocationInfo MstLoc on Mst.tLocationId = MstLoc.lId 
    join TeacherInfo Ass on assistTeacherId = Ass.tID 
    join LocationInfo AssLoc on Ass.tLocationId = AssLoc.lId