SQL Server 2012 – How to Fix Error When Trying to Run Subquery

sql-server-2012subquerywhere

I am having issues getting my subquery to run properly. Everything runs quickly and correctly until I add the subquery. I get the error:
"Msg 4145, Level 15, State 1, Line 66
An expression of non-boolean type specified in a context where a condition is expected, near ','."

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND (FP.Age, FP.PersonType) IN
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))

I've also tried EXISTS, but it returns records that are outside the Age criteria.

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND EXISTS
    (SELECT FP.Age, FP.PersonType
    FROM FactPerson FP
    WHERE (FP.PersonType = 'A')
    AND (FP.Age BETWEEN 18 AND 21))

Best Answer

The AND (a,b) IN (SELECT x,y FROM ... WHERE) is standard SQL but not yet implemented in SQL Server. You can replace it with a correlated EXISTS subquery:

AND EXISTS (SELECT * FROM ... WHERE ... AND x=a AND y=b)

Your EXISTS subquery is not correct because it is not correlated.

In your specific query, it should be written:

AND EXISTS 
    (SELECT *
    FROM FactPerson FPi
    WHERE FPi.PersonType = 'A'
    AND FPi.Age BETWEEN 18 AND 21
    AND FPi.Age = FP.Age AND FPi.PersonType = FP.PersonType)

But all the subquery is really unnecessary. The simple conditions would be equivalent:

AND FP.PersonType = 'A' 
AND FP.Age BETWEEN 18 AND 21
Related Question