Re: difficult sql statement

"Marcus Reiter" <donotSPAMME_at_microsoft.com> wrote:

>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

Try a correlated sub-query something like:

Select a.person,a.test as 'SignedUpFor',(select b.name from test where b.name not in (select b.test from signedUpTest b where a.person = b.person )) 'NeedstoSignUpFor' from signedUpTest a; Received on Tue Dec 28 2004 - 14:50:33 CST