Outer Join Problems - 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)

Outer Join Problems

Author

Topic

BlackKnight
Starting Member

8 Posts

Posted - 2007-05-02 : 05:24:20

Hi,

I have 3 tables which I need to join together to get a user's test results. The following is a simplified version.

Users: UserID(primary,int), Name(varchar)
Subjects: SubjectID(primary,int), SubjectName(varchar)
Scores: ScoreID(primary,int), UserID(int), SubjectID(int), Score(int)

So the scores table contains the scores for each user for each test.
Now not all users take all tests, but I want these to show as NULLs by using an outer join.

I currently have:

select * from scores s
join Users u on s.UserID=u.UserID
right join Subjects subs on s.SubjectID=sub.SubjectID
and u.UserID=1

Which returns something like:
ScoreID/SubjectID/Score/UserID/Name/SubjectName
1/1/80/1/Joe Bloggs/Test 1
2/2/70/1/Joe Bloggs/Test 2
NULL/3/NULL/NULL/NULL/Test 3

if Joe Bloggs did not take Test 3.

The problem is the 3rd row has NULLs for the User's name and the UserID - even though they are known because the query contains u.UserId=1.
How can I adapt the query to replace the NULLs with the user's name?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-02 : 05:49:39

BlackKnight
Starting Member

8 Posts

Posted - 2007-05-02 : 07:19:59

Thanks, that's great.
However I also have a Terms table, which contains TermID(primary,int) and TermName. (1, Autumn), (2,Winter), (3,Spring), (4,Summer)

My Scores table has an additional column for the TermID.

How do I now adapt the query to put NULLs for each term if a particular user did not take a particular test in that term.
E.g. If User 1 took Maths in term 1, but not in terms 1,2 & 3 - I want rows like
User 1, Maths, Term 1, 80
User 1, Maths, Term 2, NULL
User 1, Maths, Term 3, NULL
User 1, Maths, Term 4, NULL

and
User 1, Science, Term 1, 50
User 1, Science, Term 2, 60
User 1, Science, Term 3, 70
User 1, Science, Term 4, NULL

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-02 : 08:01:15

declare @users table (userid int , name varchar(20))
insert @users
select 1, 'joe' union
select 2, 'mark'

declare @subject table(subjectid int, subjectname varchar(20))
insert @subject
select 1, 'maths' union
select 2, 'science' union
select 3, 'history'

declare @score table(ScoreID int, UserID int, SubjectID int, Score int, termid int)
insert @score
select 1,1,1, 90,1 union
select 2,1,3, 80,3

declare @term table (termid int, termname varchar(20))
insert @term
select 1, 'Autumn' union
select 2, 'Winter' union
select 3, 'spring' union
select 4, 'summer'

select * from @users u join @subject su on u.userid=1
left outer join @score s on s.subjectid = su.subjectid and s.userid = u.userid left outer join @term t on t.termid = s.termid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources