Sql-server – Error in code in SQL Server

sql server

I made this code for SQL Server to select how many times the rider "AsdoVolante" came first in the first semester of 2017. But it does not work. What could be wrong?

Tables created in SQL Server:

CREATE TABLE Car(
IdCar INT NOT NULL,
Brand VARCHAR(20) NOT NULL,
Model VARCHAR(50),
PRIMARY KEY (IdCar));

CREATE TABLE Rider(
IdRider INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdRider));

CREATE TABLE Team(
IdTeam INT NOT NULL,
Name VARCHAR(20),
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdTeam));

CREATE TABLE Sponsor(
IdSponsor INT NOT NULL,
Name VARCHAR(20)NOT NULL,
Address VARCHAR(50),
PRIMARY KEY (IdSponsor));

CREATE TABLE Race(
IdRace INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Address VARCHAR(50) NOT NULL,
DateRace DATE NOT NULL,
NumbLaps INT NOT NULL,
BestTime TIME,
PRIMARY KEY (IdRace));

 CREATE TABLE TeamJoinsRace(
IdEnrolment INT NOT NULL,
IdTeam INT NOT NULL,
IdCar INT NOT NULL,
IdRider INT NOT NULL,
IdRace INT NOT NULL,
FinalPosition INT,
FinalRaceTime TIME,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
FOREIGN KEY (IdCar) REFERENCES Car(IdCar),
FOREIGN KEY (IdRider) REFERENCES Rider(IdRider),
FOREIGN KEY (IdTeam) REFERENCES Team(IdTeam),
PRIMARY KEY (IdEnrolment));

CREATE TABLE OficialSponsor(
IdSponsor INT NOT NULL,
IdRace INT NOT NULL,
SponsorMoney MONEY NOT NULL,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
PRIMARY KEY (IdSponsor, IdRace));

CREATE TABLE NonOficialSponsor(
IdSponsor INT NOT NULL,
IdRace INT NOT NULL,
SponsorMoney MONEY NOT NULL,
FOREIGN KEY (IdRace) REFERENCES Race(IdRace),
PRIMARY KEY (IdSponsor, IdRace));

CREATE TABLE Mechanic(
IdMechanic INT NOT NULL,
Name VARCHAR(20),
Address VARCHAR(50),
Cell INT,
PRIMARY KEY (IdMechanic));

CREATE TABLE Tuning(
IdTuning INT NOT NULL,
IdMechanic INT NOT NULL,
IdTeam INT NOT NULL,
TypeTuning VARCHAR(20) NOT NULL,
TimeTuning TIME,
DateTuning DATE NOT NULL,
FOREIGN KEY (IdTeam) REFERENCES Team(IdTeam),
FOREIGN KEY (IdMechanic) REFERENCES Mechanic(IdMechanic),
PRIMARY KEY (IdTuning));

CREATE TABLE Laps(
IdLap INT NOT NULL,
IdEnrolment INT NOT NULL,
RelativePosition INT NOT NULL,
TimeLap TIME,
FOREIGN KEY (IdEnrolment) REFERENCES TeamJoinsRace(IdEnrolment),
PRIMARY KEY (IdLap, IdEnrolment));

Code with the error:

SELECT P.DateRace, Nm.Name, epp.FinalPosition
FROM Race P, Rider Nm, TeamJoinsRace epp
WHERE epp.FinalPosition = (SELECT FinalPosition FROM TeamJoinsRace where FinalPosition = 1)
HAVING Nm.Name ='AsdoVolante' AND P.DateRace BETWEEN '2017-01-01' AND '2017-06-30';

Here is the error that I get:

Msg 8121, Level 16, State 1, Line 14

Column 'Piloto.Nome' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8121, Level 16, State 1, Line 14

Column 'Prova.Data' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8121, Level 16, State 1, Line 14

Column 'Prova.Data' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Best Answer

In most cases HAVING should only be used in a query with a GROUP BY. From BOL:

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

You don't have any aggregates so HAVING isn't the right choice. You probably want to continue to use your WHERE clause:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
AND Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';

Below is a similar query that can use HAVING (although it doesn't really make sense). Note the presence of the GROUP BY:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM Prova P, Piloto Nm, EquipaParticipaProva epp
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
GROUP BY P.Data, Nm.Nome, epp.PosicaoFinal
HAVING Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';

In terms of getting the right results, the query that you have doesn't have join clauses. You're doing a cross join between Prova, Piloto, and EquipaParticipaProva.

Perhaps this is closer to what you wanted:

SELECT P.Data, Nm.Nome, epp.PosicaoFinal
FROM EquipaParticipaProva epp
INNER JOIN Prova P ON p.IdProva = epp.IdProva
INNER JOIN Piloto Nm ON Nm.IdPiloto = epp.IdPiloto
WHERE epp.PosicaoFinal = (SELECT PosicaoFinal FROM EquipaParticipaProva where PosicaoFinal = 1)
AND Nm.Nome ='AsdoVolante' AND P.Data BETWEEN '2017-01-01' AND '2017-06-30';
Related Question