[SOLVED] Inner Join with multiple search parameters unexpectedly returning empty

Inner Join with multiple search parameters unexpectedly returning empty

Car

ID	name	ModelNo	Year
1	VW Golf	1121	2010
2	Dodge	3234	2016
3	Audi	5335	2394
4	BMW	6567	9090
5	Toyota	1221	9090

CarFeature

ID	Feature
1	ForkThickness
2	OperatorProtection
3	RearTires

CarSpec

ID	CarId	Value	CarFeatureId
1	1	1.50	1
2	1	Open Overhead	2
3	1	Summer Tires	3
4	2	1.30	1
5	2	Closed Overhead	2
6	2	Winter Tires	3
7	3	1.20	1
8	3	Closed Overhead	2
9	3	Winter Tires	3

This returns carId and car name for cars where Feature.Feature = 'ForkThickness' and the corresponding CarSpec.Value > 1.25 -- cars 1 and 2:

SELECT DISTINCT ca.Id, ca.Name 
FROM Car AS ca
INNER JOIN CarSpec AS cs ON ca.id = cs.CarId    
INNER JOIN CarFeature AS cf ON cf.Id = cs.CarFeatureId    
WHERE cf.Feature = 'ForkThickness' 
  AND cs.Value > 1.25

Including more features in the search parameters should return just car 1, but it's returning empty:

WHERE 
    Feature.Feature = 'ForkThickness' 
    AND CarSpec.Value > 1.25
    AND Feature.Feature = 'RearTires' 
    AND CarSpec.Value = 'Summer Tires'

What am I doing wrong?

Solution

You can use GROUP BY ... HAVING and conditional aggregation to assert multiple conditions. Fiddle

WITH CarsMatchingAllPredicates
     AS (SELECT cs.CarId
         FROM   CarSpec AS cs
                INNER JOIN CarFeature AS cf
                        ON cf.Id = cs.CarFeatureId
         GROUP  BY cs.CarId
         HAVING 0 NOT IN( MAX(IIF(( cf.Feature = 'ForkThickness' AND TRY_CAST(cs.Value AS DECIMAL(10, 2)) > 1.25 ), 1, 0)), 
                          MAX(IIF(( cf.Feature = 'RearTires' AND cs.Value = 'Summer Tires' ), 1, 0)) )
                        )
SELECT ca.Id,
       ca.Name
FROM   Car AS ca
WHERE  ca.Id IN (SELECT CarId
                 FROM   CarsMatchingAllPredicates)