Results 1 to 2 of 2
  1. #1
    Join Date
    May 2007

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts