Help writting a complex query
-
I'm working on a project where I need a stored procedure to generate the data I will show on a HTML table (using asp or asp.net). Last night I tried for some 4 or 5 hours to come up with the query at no avail. Now, not being precisely an SQL expert (I've just made simple queries in the past so far), I decided to bring you guys the problem to see if anyone can help.
So here it is: The database is one designed to receive information on bets made to horse races.
I have to include 3 tables in the query. The first one (hipodromo) has data relative to the information on a specific horse race track. The second one (predefinida) has data concerning the predefined races on each race track. The third one (apostable) has specific information on the races that can be betted on.
I have to get information on the next race to be run on each race track. To that end, I need to show the name of the racetrack (hipodromo.nombre), the number of the race, stored on the predefined races table (predefinida.numero) and the actual date and hour of the race, stored on the races table (apostable.corre).
The thing is that the query must generate only the next race, not all races. So far, this is the most aproximate version of the query I've managed to write:
SELECT hipo_nombre, carr_numero, carr_fecha
FROM hipodromo
JOIN (SELECT hipodromo.nombre AS hipo_nombre, MIN(apostable.corre) AS carr_fecha
FROM apostable
INNER JOIN predefinida
ON apostable.idpred=predefinida.idpred
INNER JOIN hipodromo
ON hipodromo.idhipo=predefinida.idhipo
WHERE apostable.cerrada=0
GROUP BY hipodromo.nombre) AS minimo
ON minimo.hipo_nombre=hipodromo.nombre
INNER JOIN (SELECT predefinida.idhipo, predefinida.numero AS carr_numero
FROM predefinida) AS numero
ON numero.idhipo=hipodromo.idhipo
But I get a complete listing of all races. I thought the inner joins would make the select run only once through hipodromo, but instead it ran all over apostable.
I'va also tried using the TOP n keyword, to no avail.
Any ideas?
-
Try this:
SELECT
hipodromo.nombre as hipo_nombre
, predefinida.numero as carr_numero
, (
SELECT TOP 1 apostable.corre
FROM predefinida
INNER JOIN apostable
ON predefinida.idhipo=predefinida.idhipo
WHERE predefinida.idhipo = hipodromo.idhipo
ORDER BY apostable.corre ASC
)
FROM hipodromo
WHERE hipodromo.nombre =
(
SELECT TOP 1 predefinida.idhipo
FROM predefinida
INNER JOIN apostable
ON predefinida.idhipo=predefinida.idhipo
ORDER BY apostable.corre ASC
)Edited by - jpipes on 08/12/2003 10:25:28 AM
-
Ok, thanks! I'll try it tonight and post back any results...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply