[H-GEN] SQL query needed

[Arjen Lentz]

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

Hi Paul,

On Thu, 2003-08-28 at 05:23, Paul Clarke wrote:
> 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

The above uses a type of subquery called a derived table (aka unnamed
views, a subquery in the FROM part).
Subqueries are supported in MySQL 4.1
But generally (not always), you can (re)write such queries with joins to
get the same result - often this can be faster, too.


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

Well, apart from subqueries and derived tables, in MySQL you can say
CREATE TEMPORARY TABLE name [SELECT ...] which is specific to the
connection you're in.


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

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


-- 
Arjen Lentz, Technical Writer, Trainer
Brisbane, QLD Australia
MySQL AB, www.mysql.com

Melbourne 1 December (5 days): Using & Managing MySQL Training
Training,Support,Licenses,T-shirts @ https://order.mysql.com/?marl


--
* 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/