Help me Please - 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

General SQL Server Forums

New to SQL Server Programming

Help me Please

Author

Topic

popupsql
Starting Member

5 Posts

Posted - 2009-09-29 : 04:32:00

hello
i have problem with query can anybody help me?
Show tutor id & name & student id & name for each allocation. The list must be in tutor
id sequence. Include all tutors even if they have not been allocated any students.

this is my schema for tables STUDENT , TUTOR,ALLOCATION

--STUDENT (StuId,StuSurname,StuFirstname,StuGender,City)
--PK is StuId
--FK is City References LOCATION

--TUTOR (TutorId,TutorFirstname,TutorSurname,TutorGender,HourlyRate,WorkUniCode,StudiesUniCode,City)
--PK is TutorId
--FK is WorkUniCode References UNIVERSITY
--FK is StudiesUniCode References UNIVERSITY
--FK is City References LOCATION

--ALLOCATION (TutorId,StuId)
--PK is (TutorId,StuId)
--FK is TutorId References TUTOR
--FK is StuId References STUDENT

i think i need to use tow outer join for this but it dosent work. please help me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-29 : 04:44:32

can you show us your query ?

KH
[spoiler]Time is always against us[/spoiler]

popupsql
Starting Member

5 Posts

Posted - 2009-09-29 : 04:57:38

yes here is my query

SELECT TU.TutorId,TU.TutorFirstname,AL.StuId,ST.StuFirstname
from ALLOCATION AL
LEFT OUTER JOIN STUDENT ST
ON AL.StuId=ST.StuId
LEFT OUTER JOIN TUTOR TU
ON AL.TutorId=TU.TutorId
order by TU.TutorId;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 05:20:14

Take table TUTOR in FROM clause because you want all tutors.
Left join the table ALLOCATION to the table TUTOR on TutorId.
Left join the table STUDENT to the table ALLOCATION on StuId.

No, you're never too old to Yak'n'Roll if you're too young to die.

popupsql
Starting Member

5 Posts

Posted - 2009-09-29 : 05:30:03

[quote]Originally posted by webfred

Take table TUTOR in FROM clause because you want all tutors.
Left join the table ALLOCATION to the table TUTOR on TutorId.
Left join the table STUDENT to the table ALLOCATION on StuId.

you mean like this
SELECT TU.TutorId,TU.TutorFirstname,A.StuId,S.StuFirstname
from TUTOR TU
LEFT OUTER JOIN ALLOCATON A
ON TU.TutorId=A.TutorId
LEFT OUTER JOIN STUDENT S
ON S.StuId=A.StuId
order by TU.TutorId;

popupsql
Starting Member

5 Posts

Posted - 2009-09-29 : 05:34:35

Thank you dear webfred
it works now.
cheers

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 06:06:14

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources