Avatar billede fredand Forsker
14. oktober 2004 - 11:04 Der er 3 kommentarer og
1 løsning

Tricky SQL question (hope this is right forum)

Hello!

I'm trying to get a result from 2 tables in a mysql database. The result will be a list of "hall of fame".
The tables looks like:

CREATE TABLE competitions
(
id INT NOT NULL AUTO_INCREMENT,
site_id INT NOT NULL,
startdate DATE NOT NULL,
stopdate DATE NOT NULL,
numberofcompetitors INT NOT NULL,
PRIMARY KEY(id)
);

CREATE TABLE competitors
(
id INT NOT NULL AUTO_INCREMENT,
competition_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(255) NOT NULL,
url VARCHAR(100),
image BLOB NOT NULL,
extension VARCHAR(5) NOT NULL,
votes INT NOT NULL,
customer_id INT,
PRIMARY KEY(id),
FOREIGN KEY (competition_id) REFERENCES competitions(id)
);

What i need to get hold of is:
For one certain site_id get the competitors (the winners) with highest votes where the competition they are assigned to has expired, in other words where stopdate is < then todays date.

I have tried this:

select competitions.id, competitors.name, competitors.votes 
from competitions, competitors
where
competitions.id = competitors.competition_id
and
competitions.site_id = 3
and
competitions.stopdate < '2004-10-20';

But how do I restrict it to just get the winners like votes = max(votes).

In some competitions it may also be a shared first place where more than one competitor may have the same highest votes like some other. Then I would like to get hold of booth of the winners for that competition.

So if any one could help me with this it would be great.

So the list could look like this:
Competition id    Name  Votes
1                  a    10
2                  b    30
2                  d    30
3                  f    15

Best regards
Fredrik

BTW
I'm not sure the foreign key restriction is correct but I do not think it matter for this case.
Avatar billede arne_v Ekspert
16. oktober 2004 - 21:10 #1
The primitive solution is 2 queries:

SELECT MAX(votes) AS n FROM ...

and then use the result from that f.ex. 13 to do:

SELECT ... WHERE ... votes = 13

In MS SQLServer or MySQL 4.1 you should be able to use a subquery, but not
in MySQL 3.x and 4.0
Avatar billede fredand Forsker
19. oktober 2004 - 07:44 #2
Hello!

Thanks for all help. I had to solve it with 2 separate questions. I really thougt that I should be able to combine them, but no.


SELECT competition_id, max(votes), startdate, stopdate
FROM competitors, competitions
WHERE
    site_id = ?
AND
    competitions.id = competition_id
AND
    competitions.stopdate < ?
GROUP BY
    competition_id

ORDER BY startdate DESC;



SELECT competitors.id, competitors.name, competitors.description, customers.name
FROM competitors, customers
WHERE
    competition_id = ?
AND
    votes = ?
AND
    customers.id = competitors.customer_id
ORDER BY
competitors.name;


I use it from preparedstatements in Java, the ? is changeable parameteres.

Best regards
Fredrik

BTW it is a MySQL 3,23 and please give a svar so I can give you the points.
Avatar billede arne_v Ekspert
19. oktober 2004 - 22:52 #3
I am afraid this is yet another one of the situations where sub queryes are missed
severely in MySQL before 4.1
Avatar billede fredand Forsker
20. oktober 2004 - 07:56 #4
I guess so!

Thanks mate!

/Fredrik
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester