AppletTalk.com Forum Index AppletTalk.com
Java discussions newsgroups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

[OFFTOPIC] Need help with SQL Statement

 
Post new topic   Reply to topic    AppletTalk.com Forum Index -> Java and Databases
View previous topic :: View next topic  
Author Message
Marcus Reiter
Guest





PostPosted: Tue Dec 28, 2004 8:06 pm    Post subject: [OFFTOPIC] Need help with SQL Statement Reply with quote



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


Back to top
jonck@vanderkogel.net
Guest





PostPosted: Wed Dec 29, 2004 11:51 am    Post subject: Re: Need help with SQL Statement Reply with quote




Marcus Reiter wrote:
Quote:
I got the following Problem

<snip>

Quote:

Any ideas how this could be done?


Sure, but first of all, your create statements won't work. For foreign
key constraints to work, you need to create indexes in the two tables
for the fields referencing each other. In order to get this to work,
try the following CREATE statements:

CREATE TABLE `person` (
`personId` int(5) NOT NULL auto_increment,
`name` varchar(20) default NULL,
PRIMARY KEY (`personId`),
KEY `personId` (`personId`)
) ENGINE=InnoDB

CREATE TABLE `test` (
`testId` int(5) NOT NULL auto_increment,
`name` varchar(80) NOT NULL default '',
PRIMARY KEY (`testId`),
KEY `testId` (`testId`)
) ENGINE=InnoDB

CREATE TABLE `signedUpTest` (
`personId` int(5) NOT NULL default '0',
`testId` int(5) NOT NULL default '0',
PRIMARY KEY (`personId`,`testId`),
KEY `personId` (`personId`),
KEY `testId` (`testId`),
CONSTRAINT `signeduptest_ibfk_1` FOREIGN KEY (`personId`) REFERENCES
`person` (`personId`),
CONSTRAINT `signeduptest_ibfk_2` FOREIGN KEY (`testId`) REFERENCES
`test` (`testId`)
) ENGINE=InnoDB

Now put some data in each table.
INSERT INTO person SET name = "Jonck";
INSERT INTO test SET name = "Spelling";
INSERT INTO test SET name = "Math";
INSERT INTO test SET name = "SQL";
INSERT INTO signedUpTest SET personId = 1;

To see for which tests a person has signed up:
SELECT test.name AS testName, person.name AS personName FROM test,
person, signedUpTest AS sign WHERE person.personId=sign.personId AND
test.testId=sign.testId AND person.personId=1;

Result:
+----------+------------+
Quote:
testName | personName |
+----------+------------+
Spelling | Jonck |
+----------+------------+


To see which tests a person can still sign up for you will need a
sub-select statement:
SELECT name AS testName FROM test WHERE testId NOT IN (SELECT testId
FROM signedUpTest WHERE personId=1);

Result:
+----------+
Quote:
testName |
+----------+
Math |
SQL |
+----------+


So anyway, now I've handed you the fish, it's probably a good idea that
you get a book on SQL and do some studying if you ever want to be able
to fish for yourself.

Cheers, Jonck


Back to top
jonck@vanderkogel.net
Guest





PostPosted: Wed Dec 29, 2004 11:55 am    Post subject: Re: Need help with SQL Statement Reply with quote



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

Back to top
Marcus Reiter
Guest





PostPosted: Wed Dec 29, 2004 1:35 pm    Post subject: Re: Need help with SQL Statement Reply with quote

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?


Back to top
Marcus Reiter
Guest





PostPosted: Wed Dec 29, 2004 1:51 pm    Post subject: Re: Need help with SQL Statement Reply with quote

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


Back to top
jonck@vanderkogel.net
Guest





PostPosted: Wed Dec 29, 2004 2:01 pm    Post subject: Re: Need help with SQL Statement Reply with quote

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

Back to top
IchBin
Guest





PostPosted: Thu Dec 30, 2004 1:14 am    Post subject: Re: Need help with SQL Statement Reply with quote

Marcus Reiter wrote:
Quote:
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);


Just create VIEWS for your individual queries.


--


Thanks in Advance...
IchBin
__________________________________________________________________________

'The meeting of two personalities is like the contact of two chemical
substances:
if there is any reaction, both are transformed.'
- Carl Gustav Jung, (1875-1961), psychiatrist and psychologist

Back to top
Harald Fuchs
Guest





PostPosted: Thu Dec 30, 2004 5:30 pm    Post subject: Re: Need help with SQL Statement Reply with quote

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

Quote:
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".

This would be cross-tabulation, something impossible in standard SQL.

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

IMHO that's neither possible - you need to join every person with
every test in order to determine whether it's signed up or not.
Something like that:

SELECT p.name, t.name,
CASE s.testId WHEN t.testId THEN 'signed up' ELSE 'not signed up' END
FROM person p
CROSS JOIN test t
LEFT JOIN signedUpTest s ON s.personId = p.personId AND s.testId = t.testId

Back to top
Display posts from previous:   
Post new topic   Reply to topic    AppletTalk.com Forum Index -> Java and Databases All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
SEO toolkit © 2004-2006 webmedic.