Outer join problem - hardest query ever?

[JohnCaver]

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.

 

[bborissov]

Something like:

[COLOR=blue]SELECT[/color] *
     [COLOR=blue]FROM[/color] Users
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] * [COLOR=blue]from[/color] Trials
                  [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] Scores [COLOR=blue]ON[/color] Trials.TrialId = Scores.TrialId) Test
[COLOR=blue]ON[/color] Usres.UserId = Test.UserId

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[JohnCaver]

Thanks, but gives the error message:
The column 'TrialId' was specified multiple times for 'Test'.

 

[r937]

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

r937.com | rudy.ca

 

[onpnt]

That's why it's the "hardest query ever". Because it's impossible ;-)

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[JohnCaver]

Sorry - obviously it doesn't - I just listed it twice by mistake.

 

[bborissov]

Instead of using * in the select statement just add the field list you want.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP