I made this code for SQL Server to select how many times the pilot "AsdoVolante" came first in the first half of 2017. However, it does not work. What could be wrong?
SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
HAVING Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';
You can get this information by linking information with JOIN and restricting WHERE :
SELECT Nm.Nome,
COUNT(epp.PosicaoFinal) AS quantidade
FROM Prova P
INNER JOIN EquipaParticipaProva epp ON epp.IdProva = P.IdProva
INNER JOIN Piloto Nm ON epp.IdPiloto = epp.IdPiloto
WHERE epp.PosicaoFinal = 1
and Nm.Nome = 'AsdoVolante'
and P.Data BETWEEN '2017-01-01' AND '2017-06-30';
GROUP BY Nm.Nome
SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT epp.PosicaoFinal FROM EquipaParticipaProva where epp.PosicaoFinal = 1)
HAVING Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';
You must first check which error is displaying.
In any case, try the select below: SELECT P.Data, Nm.Name, epp.PosicaoFinal FROM Proof P, Pilot Nm, TeamParticipaProva epp WHERE epp.PosicaoFinal = 1 AND Nm.Name = 'AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';