difficult sql statement

Hallo,

Ich 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 Received on Tue Dec 28 2004 - 14:04:02 CST