Re: difficult sql statement

With your help, I got the following to work: Alle possible tests:
select s.person, a.test from test a join person s)

All Tests that have not been signedup for yet: select b.test, s.personalNr from test b join person s where b.test not in (select a2.test from signedUpTest a2 where s.personalNr = a2.person);

All Tests that people have signedup already: select b.test, b.person from signedUpTest b;

Tests that have been signed up combined with those that have not been written yet:
Select d.person, d.test 'SignedUpFor', c.test 'NeedstoSignUpFor' from signedUpTest d
join (select b.test , s.personalNr from testb join person s where b.test not in (select a2.test from signedUpTest a2 where s.personalNr = a2.person)
) c on (d.person = c.personalNr);

However, that is not really what I was looking for. Especially the last one is crap - that's a carthesian product and doesn't make any sense...

I would like to create a list that shows all possible tests as columns and all students as rows and then
some boolean value that shows me wether or not a student has signed up for this test yet or not.

Would that be possible somehow?

Marcus Received on Wed Dec 29 2004 - 07:47:59 CST