Trickin Query ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Trickin Query ?

Post by pookie62 »

Hi all,
I've got this query:

"SELECT
`klasse`.`Klasse`,
`deelnemer`.`Naam` AS `Deelnemer`,
`deelnemer`.`Vereniging` AS `Vereniging`,
`deelnemer`.`URL` AS `URL`,
`wedstrijd`.`Naam` AS `Wedstrijd`,
`wedstrijd`.`Datum`,
`wedstrijd`.`Plaats` AS `Plaats`,
TRUNCATE(`score`.`Tijd` ,2) AS `Tijd`
FROM `deelnemer`
INNER JOIN `inschrijving` ON (`deelnemer`.`Id` = `inschrijving`.`DeelnemerId`)
INNER JOIN `wedstrijd` ON (`inschrijving`.`WedstrijdId` = `wedstrijd`.`Id`) INNER JOIN `klasse` ON (`inschrijving`.`KlassId` = `klasse`.`Id`)
INNER JOIN `score` ON (`inschrijving`.`Id` = `score`.`InschrijvingId`) WHERE `wedstrijd`.`Naam` = ('$w_naam')
ORDER BY `klasse`.`Klasse`, `Tijd` ASC";

Actually I want to use this:
TRUNCATE (MIN(`score`.`Tijd`, 2)) AS `Tijd` FROM `deelnemer`
But now I have to use GROUP BY to get this working.
I don't want to GROUP BY because that's done by selecting the match I want to display the results from.

Any ideas to trick this ??
A little background info:
Every contester (deelnemer) has per match(wedstrijd) five results, from these five scores I want to display the smallest number and sort on it (Tijd)
Thanks in advance for replying !
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Trickin Query ?

Post by timvw »

pookie62 wrote: I don't want to GROUP BY because that's done by selecting the match I want to display the results from.
I really don't understand what you are trying to say here :(

But what is wrong with GROUP BY deelnemer.Id ??
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi Timvw,
Oke let me try to explain a bot more..
I have a selectbox in which all matches are selectable. (So here is the GROUP BY done) The match you click on must be displayed. But the smallest value of five is the one that counts for the result of the competitor.
I am looking for a way to calculate this smallest number, because now I get all five results in the output table. This screws up the ranking.
E.g. the person with the smallest value in OKP is the winner in OKP section
What I want for results is e.g.
1.OKP John Doe : 5,56
2.OKP Jane Doe : 12,87
3. OKP Pietje Puk: 20,58

1. SKP John Puk :8,90
2. SKP Jane Puk : 27,98

Hope this makes sense... :)
Post Reply