[H-GEN] SQL query needed
Paul Clarke
paul at qfm.org.au
Thu Aug 28 17:39:16 EDT 2003
[ Humbug *General* list - semi-serious discussions about Humbug and ]
[ Unix-related topics. Posts from non-subscribed addresses will vanish. ]
Thanks Arjen,
Worked a treat! I really need to sit down and study the syntax of mySQL
as soon as I get time :)
Paul
Site Dynamics - Paul Clarke
paul at sitedynamics.com.au
0414 367 747
-----Original Message-----
From: Arjen Lentz [mailto:arjen at mysql.com]
Sent: Thursday, 28 August 2003 11:04 AM
To: general at lists.humbug.org.au
Cc: Paul Clarke
Subject: RE: [H-GEN] SQL query needed
Ok, here is your solution:
SELECT s.studid,s.name
,MAX(IF(t.testid=1,r.result,'')) as maths1
,MAX(IF(t.testid=2,r.result,'')) as english1
FROM tests t,results r,students s
WHERE t.testid = r.testid AND s.studid = r.studid
GROUP BY s.studid
ORDER BY s.studid;
This is the result I get:
+--------+------+--------+----------+
| studid | name | maths1 | english1 |
+--------+------+--------+----------+
| 1000 | Bill | A | B |
| 1002 | Sue | B | C |
+--------+------+--------+----------+
You will need to build the list of subjects first, with another query:
SELECT s.testid,s.description FROM tests;
And use PHP to build put one ",MAX..." line in the main query for each
subject. You just fill in the 1,2,etc (testid) and the column alias
maths1,english1,etc.
Using this method, the main query will produce your full result without
further mangling. Pretty straightforward.
You can add extra niftyness by using a LEFT JOIN, to take into account
the fact that some students may have missed a test.
Regards,
Arjen.
> 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');
--
* 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/
More information about the General
mailing list