[H-GEN] SQL query needed

[Paul Gearon]

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

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

Doh!  Of course it won't work the say I said.  Sorry about that.  :-}

So I looked at it, and it's beyond my meager SQL skills.  I can get one 
result at a time, and I can union them all... something like:

select students.studid, students.name, description, result as 'maths1', 
null as 'english1' from ((students left join results on (students.studid 
= results.studid and results.testid = 1)) left join tests on 
results.testid = tests.testid) union select students.studid, 
students.name, description, null as 'maths1', result as 'english1' from 
((students left join results on (students.studid = results.studid and 
results.testid = 2)) left join tests on results.testid = tests.testid);

But that doesn't merge the lines.  They could be inserted into a 
temporary table and then a max applied to each result column, but that's 
2 SQL queries, and now it's just getting silly.

I'd build it programmatically.  It'd work much better I think.  :-)

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