Can it be One Statement or programming?

[DJDaveMark]

Hi all,

I'm fairly new to SQL & I was wondered if there was a single SQL satement that would create something like the following table...

            race1    race2    race3    race4

a. person     9        8        8        8

b. person     5        5        4        1

c. person     9        7        4        7

d. person     4        8        5        4

However these are all the best results for every race & every person in the database.

At the moment I'm querying the best result for all of the people in a single race then using some programming to do this for every race. This is proving to be very hard to maintain.

The tables are people, race_names & results

Any rough help will be much appreciated, even if that means saying "it can't be done!".

Thanks, DJDaveMark

 

[Golom]

Some database systems (Access for example) support cross-tab or pivot table queries. If yours has such a capability then that may be a solution for you.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[DJDaveMark]

Thanks for that Golom,

Will have a look into those, and post back where it leads me.

Much appreciated, cheers, DJDaveMark

 

[DJDaveMark]

Thanks again Golom, you were spot on, here's where it led me...

SELECT firstName, lastName, MAX( score ) AS Best
FROM results, people
WHERE people_id = p_id
GROUP BY people_id

which gave me...

firstName  lastName  Best
   a        person     9
   b        person     8
   c        person     9

but where I was stuck for a while was how to get the best score for only one race. I fiddled around with an IF and came up with what I needed...

SELECT
    firstName,
    lastName,
    MAX( IF(race_name='race1', score, NULL) ) AS race1,
    MAX( IF(race_name='race2', score, NULL) ) AS race2,
    MAX( IF(race_name='race3', score, NULL) ) AS race3,
    MAX( IF(race_name='race4', score, NULL) ) AS race4
FROM results, people, race_names
WHERE people_id = p_id
AND race_id = r_id
GROUP BY people_id

which gives me...

            race1    race2    race3    race4

a. person     9        8        8        8

b. person    NULL      5        4       NULL

c. person     9       NULL      4        7

d. person     4        8       NULL     NULL

the NULLs being where someone hasn't done that race.

Merci beaucoup once again, DJDaveMark

 

[DJDaveMark]

Just wondered something else,

Would there be any way to get the race_id which belongs to each particular best score i.e...

            race1  [b][COLOR=black yellow]race1_id[/color][/b]  race2    race3    race4

a. person     9        [b][COLOR=black yellow]1[/color][/b]       8        8        8

b. person    NULL     [b][COLOR=black yellow]NULL[/color][/b]     5        4       NULL

c. person     9        [b][COLOR=black yellow]3[/color][/b]      NULL      4        7

d. person     4        [b][COLOR=black yellow]4[/color][/b]       8       NULL     NULL

I'm using mySQL 4.1.7 and have played around with User-Defined Variables with no success. Can anyone point my in a direction? I was thinking something like this (which doesn't work!)...

SELECT
    firstName,
    lastName,
    [b][COLOR=black yellow]@best:=[/color][/b]MAX( IF(race_name='race1', score, NULL) ) AS race1,
    [b][COLOR=black yellow]IF(score=@best, race_id, NULL) AS 'race1_id',[/color][/b]
    MAX( IF(race_name='race2', score, NULL) ) AS race2,
    MAX( IF(race_name='race3', score, NULL) ) AS race3,
    MAX( IF(race_name='race4', score, NULL) ) AS race4
FROM results, people, race_names
WHERE people_id = p_id
AND race_id = r_id
GROUP BY people_id

Although this time I wouldn't be surprised if it couldn't be done.
But it would be wonderful it it could!

Thanks, DaveMark