BIG SQL Statement

Hi,

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 signedUpTest 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 signedUpTest an)

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


Any ideas how this could be done?


Thanks,

Marcus

Basic select statement:

SELECT person.personalNr, test.name, sigtest.person
FROM person JOIN test LEFT JOIN signedUpTest AS sigtest ON person.personalNr
 = sigtest.person AND test.name = sigtest.test

When last column has a value, then the person (first column) is signed up to
 the test (second column).
When there's NULL in the last column, then the person is NOT signed up to th
e test, so:

1. To extract only those tests (for each person) to which they are not signe
d add this "where" clause
(you may also remove the last column from select statement):

WHERE sigtest.person IS NULL

2. To extract only those tests (for each person) to which they are signed, a
dd this "where" clause
(you may also remove the last column from select statement):

WHERE sigtest.person IS NOT NULL

This covers all cases (the select solves problems 1 and 4, and "where" claus
es solve 3 and 2).


Hilarion

THANKS!

That was exactly what I was looking for!

Now I changed it a bit and got that:

SELECT s.personalNr PersonalNr, a.test, CASE WHEN b.person IS NULL THEN
'false' ELSE 'true' END 'Signed Up'
FROM person s JOIN signedUpTest a LEFT JOIN test b ON s.personalNr =
b.person AND a.test = b.test order by s.personalNr, b.person DESC;

( Actually my real one is even more complicated - one more join, but that
was irrelevant)...

Marcus

> THANKS!

No problem.


> Now I changed it a bit and got that:
>
> SELECT s.personalNr PersonalNr, a.test, CASE WHEN b.person IS NULL THEN
> 'false' ELSE 'true' END 'Signed Up'
> FROM person s JOIN signedUpTest a LEFT JOIN test b ON s.personalNr =
> b.person AND a.test = b.test order by s.personalNr, b.person DESC;

I was not sure of CASE syntax for MySQL (I do not use one), that's why I did
not suggest it. If you are going to use CASE in select with the "where" clau
ses
I suggested, then do not change the "WHERE sigtest.person IS NULL"
or "WHERE sigtest.person IS NOT NULL" to "CASE" version - it'll make
the select slower.

Hilarion

Hm.

I thought I was finished and now -

I tried to get the last bit done - I want to replace 'a.test' by it's full
name that you find in another table.

However, if I add that to the joins I get a huge list of values, propably
some sort of the cartesian product again
( because of the one left join I guess).

What I want to add:
select ....p.longname.... join fulltest p on (p.shortname= b.test)


Thanks for your help!!!!!!!!

Marcus

Look for the order of tables in JOINs (and their aliases - I'm not sure if y
ou didn't mix them up in
JOIN ON clauses in the query you showed before, so I changed aliases "a", "b
", etc. to "t" for "test",
"p" for "person", "ft" for "fulltest" and "st" for "signedUpTest") and join 
"fulltest" with "test" (in ON
clause), not with "signedUpTest" (which does not always contain info, cause 
it's LEFT JOINed).

Do it this way (I'm not sure of ORDER BY - I left it as you gave it, but am 
not sure if it's what you
really wanted):

SELECT
p.personalNr,
t.test,
ft.longname,
CASE
WHEN (su.person IS NULL) THEN 'false'
ELSE 'true'
END AS signedUp
FROM
person AS p JOIN
test AS t JOIN
fulltest AS ft ON (ft.shortname = t.test) LEFT JOIN
signedUpTest AS st ON ((p.personalNr = st.person) AND (t.test = st.test))
ORDER BY
p.personalNr,
su.person DESC


Hilarion

Thanks sooo MUCH!

Your help was GREAT!

How comes that you are sooo good in databases?
How many years of experience do you got????

The mistake I did was that I put the conditions all together into the last
join -
I didn't know you could give each join their own conditions!!!

Now that I have this list I have to think of a good way of how to use it -
I need to create checkboxes for each of those tests where you can sign up or
unsign
by checking / unchecking those checkboxes.

I thought I maybe should take an array and then print in the name of the
test
and then "true" or "false into the next dimensions field to see wether or
not a person
has signed up for that test.

But then how do I know which of these rows I need to update and which I can
just leave as they are?

What's the better / easier design?
Would you compare the array before / after and then only update those values
that have actually changed
or would you delete all rows of that person from the database and create
/update all of  them again?

Marcus

> Thanks sooo MUCH!

No problem.


> How comes that you are sooo good in databases?
> How many years of experience do you got????

I'm not THAT good.
My experience? Half year of learnig (relational DBs mostly, some SQL),
2 years of using/designing (mostly PL/SQL for Oracle 8i and Transact-SQL for
MS SQL Server 2000).


> The mistake I did was that I put the conditions all together into the last
> join -
> I didn't know you could give each join their own conditions!!!

:) Look for some tutorial for SQL standard (and check MySQL documentation).


> Now that I have this list I have to think of a good way of how to use it -
> I need to create checkboxes for each of those tests where you can sign up 
or
> unsign
> by checking / unchecking those checkboxes.
>
> I thought I maybe should take an array and then print in the name of the
> test
> and then "true" or "false into the next dimensions field to see wether or
> not a person
> has signed up for that test.
>
> But then how do I know which of these rows I need to update and which I ca
n
> just leave as they are?
>
> What's the better / easier design?
> Would you compare the array before / after and then only update those valu
es
> that have actually changed

Not very bad idea, but requires passing "before" values between PHP pages
(which allows some user manipulations) or extra select to retrieve them.

I hope you do not want to check each checkbox individually, but use somethin
g
like ($personalNr is a PHP var storing the user, $checkedTests and
$testsCheckedBefore are strings containing comma separated list of checked
test names / ids "after" and "before"):

DELETE FROM signedUpTest
WHERE (person = $personalNr)
AND NOT (test IN ( $checkedTests ) );

INSERT INTO signedUpTest ( person, test )
SELECT $personalNr AS person, name AS test
FROM test
WHERE (name IN ( $checkedTests ) )
AND (NOT name IN ( $testsCheckedBefore ) );

In newer versions of MySQL you could replace $testsCheckedBefore with
subselect from signedUpTest (for the $personalNr).


> or would you delete all rows of that person from the database and create
> /update all of  them again?

I do not remember how MySQL handles transactions. If you do not use them,
then some script error after DELETE might cause leaving all tests for the
user unsubscribed.



If it wasn't MySQL, then you could also use stored procedures, which would
check if record needs to be deleted or inserted (you'd only have to pass
new tests list to the procedure).


Hilarion

> I hope you do not want to check each checkbox individually, but use
something
> like ($personalNr is a PHP var storing the user, $checkedTests and
> $testsCheckedBefore are strings containing comma separated list of checked
> test names / ids "after" and "before"):

What do you mean with that?

(On a side note I have to admit I am writing the code in Java,
I just used this newsgroup because it covers sql.)

My idea was to have one variable for each checkbox.
How else would I do it? I mean I need to check which checkbox has been
checked and
which has been unchecked and then I need to delete or insert values for each
of these checkboxes...

?

Marcus

> (On a side note I have to admit I am writing the code in Java,
> I just used this newsgroup because it covers sql.)

I do not know how does Java work on server side, but I assume
that most functionalities available in PHP can be achieved in Java (JSP?).


> My idea was to have one variable for each checkbox.

If you "declare" your checkbox input fields in HTML this way:
<input type="checkbox" name="my_checkbox_array[]" value="individual_checkbox
_ID" />
then you'll recieve checkbox selections in one variable which is an array (h
olding
only IDs of checked checkboxes).


> How else would I do it? I mean I need to check which checkbox has been
> checked and
> which has been unchecked and then I need to delete or insert values for ea
ch
> of these checkboxes...

It does not matter if you use one variable for all checkboxes or one for eac
h checkbox.
What does matter is the way you update the data in your DB. You can (as you 
wrote it)
check and update each one separately, but you can also check and update all 
at
one time as I described before:

-- delete all those, which were selected, but now are not selected
DELETE FROM signedUpTest
WHERE (person = $personalNr)
AND NOT (test IN ( $checkedTests ) );

-- insert those, which were not selected, but now are selected
INSERT INTO signedUpTest ( person, test )
SELECT $personalNr AS person, name AS test
FROM test
WHERE (name IN ( $checkedTests ) )
AND (NOT name IN ( $testsCheckedBefore ) );

You'll have to:
1. replace $personalNr with value of the variable which holds personalNr
of the person which is operating the form (selecting test for oneself),
2. replace $checkedTests with a comma separated list of test identifiers (te
st names) of the
tests which the user has selected (concatenate values comming from the HTML 
form
checkboxes),
3. replace $testsCheckedBefore with a comma separated list of test identifie
rs (test names)
of the tests which were selected by the user before (according to info store
d in DB), which
can be retrieved by statement like this:
SELECT GROUP_CONCAT( test ) FROM signedUpTest WHERE person = $personalNr;

Hilarion