Monday, March 26, 2012

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.this cannot be correct --

Scores - scoreid(primary,int), userid(int), trialid(int), userid(int), testid(int), score(int)

you cannot have two columns in the same table with the same name

No comments:

Post a Comment