[H-GEN] SQL query needed

[Dale Stewart]

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

hmmmmmmm.....

my apologies for trying to help then.....it appears that my brain does 
not function to well at 6.30 in the morning.......I'll shut up from now 
on........

Paul Gearon wrote:

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



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