[OFFTOPIC] Need help with SQL Statement

From: Marcus Reiter (donotSPAMME_at_microsoft.com)
Date: 12/28/04

  • Next message: BoraT: "Creating DB" 
    Date: Tue, 28 Dec 2004 21:06:03 +0100

    I got the following Problem

    I have 3 tables. One table with Persons, they can sign up for tests.
    One table that contains all possible tests, one table that shows which
    person has signed up for which test(s).

    Now I would like to create a list that shows me who signed up to which test
    and which person can still sign up for which tests.

    Either using one big table where there is null for the tests that a person
    has not signed up yet, or one list,
    that shows me only those tests, a person could still sign up for.
    How can I get this done? I am trying to get this done for hours now, and
    can't find a solution.

    Should I use a left join? Or with "Minus"?

    Any ideas?

    Here are my (mysql) Create Statements ( only the necessary attributes
    shown):

    create table person (
    personalNr DECIMAL not null,
    primary key(personalNr)
    )

    create table test(
    name char(80) not null,
    primary key(name)
    );

    create table signedUpTest(
    person DECIMAL not null,
    test char(80) not null,
    primary key(person, test),
    foreign key(test) references test(name),
    foreign key(person) references person(personalNr)
    );

    Here a few of the things I tried (Only 1 and 2 work):

    1. All possibilities

    select p.personalNr, t.name from person p join test t

    2. All tests a person actually has signed up yet:

    select an.person, an.test from angemeldetePruefung an;

    3. All tests that a person could still sign up to:

    select p.personalNr, t.name from person p join test t minus (select
    an.person, an.test from angemeldetePruefung an)

    4. All signed up and not yet signed up yet tests

    Any ideas how this could be done?

    Thanks,

    Marcus

  • Next message: BoraT: "Creating DB"