3 table joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

3 table joins

Author

Topic

scptech
Starting Member

8 Posts

Posted - 2008-05-16 : 11:44:27

I have 3 tables, students (studentId, studentName), classes (classId, className) and studentclass (studentId, classId, sessionId)

Want to display all studentnames, classnames and sessionIds, regardless of whether the student is enrolled in a specific class or not.
So the result set should show all students and all classes:
studentname classname sessionId
----------- --------- ---------
john english 2
john math <null>
jane english <null>
jane math 3

Please help in how to write such SQL?
Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 11:50:06

Can i see what you tried till now?

scptech
Starting Member

8 Posts

Posted - 2008-05-16 : 11:54:58

I have nothing to show you, since it is the first time I am trying something like this.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-16 : 12:30:28

SELECT s.studentName,c.className,sc.sessionId
FROM students s
LEFT JOIN studentclass sc
ON sc.studentId = s.studentId
LEFT JOIN classes c
ON c.classId=sc.classId

Make sure you read about joins on books online to learn how they works.

scptech
Starting Member

8 Posts

Posted - 2008-05-16 : 12:43:38

Thank you.
Your SQL is not Ok, it will not give all the rows in both students and classes. If you have 2 rows in students and 2 in classes, and 0 in studentclasses, the result set should show 4 rows with nulls in the last column.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-16 : 12:52:25

[code]DECLARE @students TABLE (studentId INT, studentName VARCHAR(20))
DECLARE @classes TABLE (classId INT, className VARCHAR(20))
DECLARE @studentclass TABLE (studentId INT, classId INT, sessionId INT)

INSERT @Students
SELECT 1, 'Frank'
UNION ALL SELECT 2, 'Billy'

INSERT @Classes
SELECT 1, 'Math'
UNION ALL SELECT 2, 'English'

INSERT @studentclass
SELECT 1, 2, 100
UNION ALL SELECT 2, 1, 999

SELECT s.studentName,c.className,sc.sessionId
FROM @students s
CROSS JOIN @classes c
LEFT JOIN @studentclass sc
ON sc.studentId = s.studentId
AND sc.classId = c.classId[/code]

scptech
Starting Member

8 Posts

Posted - 2008-05-16 : 15:07:13

thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources