Error When Trying to Run Subquery

Question

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))

Answer 1

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

Answer 2

You need to break apart your AND (FP.Age, FP.PersonType) INpart. The following code parses correctly and should provide the data you want.

AND (
        FP.Age
    IN (
        SELECT FP.Age

        FROM FactPerson FP
        WHERE (FP.PersonType = 'A')
            AND (FP.Age BETWEEN 18 AND 21)
        )
        AND FP.person IN (
        SELECT FP.person

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