AppletTalk.com

Marcus Reiter · Guest

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

jonck@vanderkogel.net · Guest

Marcus Reiter wrote:

jonck@vanderkogel.net · Guest

Small correction, the last INSERT statement should be:
INSERT INTO signedUpTest SET personId = 1, testId =1;

Marcus Reiter · Guest

Yeah,

this worked all fine.

Now I just wonder how I could combine that -

I would like to get a table that shows me all different tests on the top as
columns and
all students to the left as rows and then I would like to see a "signed up"
or "not signed up".

Or some way of how I could combine the table "signed up" with the table "not
signed up" without
getting a karthesian product...

Any ideas?

Marcus Reiter · Guest

This is what I got now:

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);

jonck@vanderkogel.net · Guest

You're not going to be able to do that with SQL, you'll have to create
an app (in Java for example, since you're posting to this newsgroup)
that does the queries (using JDBC) and combines them (in a JTable for
example).

IchBin · Guest

Marcus Reiter wrote:

Harald Fuchs · Guest

In article <cqubt6$f2k$03$1 (AT) news (DOT) t-online.com>,
"Marcus Reiter" <donotSPAMME (AT) microsoft (DOT) com> writes: