[H-GEN] SQL query needed

[Paul Clarke]

[ Humbug *General* list - semi-serious discussions about Humbug and     ]
[ Unix-related topics. Posts from non-subscribed addresses will vanish. ]

Actually, we are still not there yet. Because all the results are in the
same field the "max(maths1) as maths1" won't work because there is no
field named maths1, only one called result (which is associated to a
test via the testid field in results).

I can only see it work if there is some way of having a query within a
query. Something like:

Select students.name, studnts.class, r1.result as maths1, r2.result as
english1 from students, (select studid, result from results where
testid=1) as r1, (select studid, result from results where testid=2) as
r2 where students.studid=r1.studid and students.studid=r2.studid order
by students.name

Of course "MySQL said: You have an error in your SQL syntax near
'(select studid, result from results where testid=1) as r1, " because it
won't do this temporary table thing I want

Many years ago I did some study on sql and have it in the back of my
mind that there was some way of creating a temporary table ( that's
where it all gets a bit foggy)

Below is the dump in case anyone wants to have a play with the problem.

Thanks

Paul.


CREATE TABLE results (
  studid varchar(6) NOT NULL default '',
  testid tinyint(11) NOT NULL default '0',
  result char(1) NOT NULL default '',
  PRIMARY KEY  (studid,testid)
) TYPE=MyISAM;

INSERT INTO results VALUES ('1000', 1, 'A');
INSERT INTO results VALUES ('1002', 1, 'B');
INSERT INTO results VALUES ('1000', 2, 'B');
INSERT INTO results VALUES ('1002', 2, 'C');

CREATE TABLE students (
  studid varchar(6) NOT NULL default '',
  name varchar(20) NOT NULL default '',
  class varchar(5) NOT NULL default '',
  PRIMARY KEY  (studid)
) TYPE=MyISAM;

INSERT INTO students VALUES ('1000', 'Bill', '5a');
INSERT INTO students VALUES ('1002', 'Sue', '5a');

CREATE TABLE tests (
  testid tinyint(4) NOT NULL default '0',
  description varchar(20) NOT NULL default '',
  PRIMARY KEY  (testid)
) TYPE=MyISAM;

INSERT INTO tests VALUES (1, 'maths1');
INSERT INTO tests VALUES (2, 'english1');



-----Original Message-----
From: Majordomo [mailto:majordom at caliburn.humbug.org.au] On Behalf Of
Paul Gearon
Sent: Wednesday, 27 August 2003 5:24 PM
To: general at lists.humbug.org.au
Subject: Re: [H-GEN] SQL query needed


[ Humbug *General* list - semi-serious discussions about Humbug and
]
[ Unix-related topics. Posts from non-subscribed addresses will vanish.
]

Dale Stewart wrote:
> The query would look something like
> 
> select Students.STUDID, Students.name, maths1, english1 from Students,
> Results, Tests
> where Students.STUDID = Results.STUDID
> and Results.TESTID = Tests.TESTID
> order by Students.STUDID
> 
> I think thats should work

Firstly, no, it won't work as you're expecting.  Secondly, I don't think

you're providing what Paul wants (though I might have that wrong).

The data that this query returns will have duplicate student lines, with

every line indicating a different test score, and all the other scores 
being null.  With the example data given (I've included it at the end) 
the result will look like:
  STUDID, name, maths1, english1
  1000, Bill, A, (null)
  1000, Bill, (null), B
  1002, Sue, B, (null)
  1002, Sue, (null), C

(caveat emptor: I'm doing this off the top of my head... I haven't typed

this into a DB to check the results)

To merge the rows you can use agregate functions such as max().  This 
would modify the query to be something like this:

  select Students.STUDID, Students.name, max(maths1) as maths1, 
max(english1) as english1 from Students, Results, Tests
  where Students.STUDID = Results.STUDID
  and Results.TESTID = Tests.TESTID
  group by Students.STUDID, Students.name
  order by Students.STUDID

(I recall a DB recently wanting to ALSO "group by" the max() functions. 
  It might have been MySql.  Play with the query to get what you want)

However, I suspect that Paul may have wanted the columns for each 
subject to be automatically generated (since "maths1" and "english1" are

both entries in the "Tests" table).  I don't actually know of an SQL way

to do this in MySql (Arjen, can you help me here?).  It is possible in 
other databases, eg. <cough, cough> MSAccess does it with pivot queries.

The way to do this would be to write some php code which queries "Tests"

and then uses the results to build an SQL query like the one above. 
It's pretty easy to do, it just isn't a single SQL query that does it 
all in one step.

>> Students = STUDID, name, class
>> 1000, Bill, 5a
>> 1002, Sue, 5a
>>
>> and
>> Results = STUDID, TESTID, result
>> 1000, 1, A
>> 1002, 1, B
>> 1000, 2, B
>> 1002, 2, C
>>
>> and
>> Tests = TESTID, description
>> 1, maths1
>> 2, english1
>>
>> I want to have a query that will return:
>> STUDID, name, maths1, english1
>> 1000, Bill, A, B
>> 1002, Sue, B, C


-- 
Regards,
Paul Gearon

Software Engineer                Telephone:   +61 7 3876 2188
Plugged In Software              Fax:         +61 7 3876 4899
http://www.PIsoftware.com        PGP Key available via finger

Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum
immane mittam. (Translation from latin: "I have a catapult. Give me all
the money, or I will fling an enormous rock at your head.")



--
* This is list (humbug) general handled by majordomo at lists.humbug.org.au
.
* Postings to this list are only accepted from subscribed addresses of
* lists 'general' or 'general-post'.  See http://www.humbug.org.au/


--
* This is list (humbug) general handled by majordomo at lists.humbug.org.au .
* Postings to this list are only accepted from subscribed addresses of
* lists 'general' or 'general-post'.  See http://www.humbug.org.au/