Querying table more than once - 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)

Querying table more than once

Author

Topic

Gizmo
Starting Member

2 Posts

Posted - 2008-06-12 : 08:52:22

Hi All

Thx in advanced for any help anyone can offer me.

I have 2 tables Staff and Class which are as follows

Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .

Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID

LessonTemplateID and TermID link to other tables which i don't need right now.

The Class.TeacherID and Class.AssistantID both link to the Staff.ID

I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.

If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.

Im really stuck on this and just can't figure it out (is it even possible?).

Thx for any help Scott.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 09:10:09

[code]SELECT COALESCE(teacher.FirstName,'') + '' +COALESCE(teacher.MiddleName,'') + ' ' + COALESCE(teacher.LastName,''),
COALESCE(assistant.FirstName,'') + ' ' +COALESCE(assistant.MiddleName,'') + ' '+COALESCE(assistant.LastName,'')
FROM Class c
INNER JOIN Staff teacher
ON c.TeacherID=teacher.ID
INNER JOIN Staff assistant
ON c.AssistantID=assistant.ID
WHERE c.ID=12[/code]

Gizmo
Starting Member

2 Posts

Posted - 2008-06-12 : 09:29:21

Thx so much. been trying to solve that problem all day and uv done it in a few mins for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources