Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Trials
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialidsql