[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. ]

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/