Inner Join with multiple search parameters unexpectedly returning empty

Question

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?

Answer 1

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) 

Answer 2

You code is looking for something that has both Feature.Feature = ForkThickness AND Feature.Feature = RearTires.

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

This could be used with an OR statement instead to find options, otherwise you can remove the double "Feature.Feature" parameter/requirement.

Instead, replace the AND statement with an OR statement:

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

This would result in 2 cars being found.

Alternatively, if you are looking to get 1 result, you could use the following. But just check your logic depending on what you actually require.

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

Answer 3

The problem with your attempt is that you are adding further join conditions, so you are looking for a single spec/feature with both conditions. Whereas actually you want to check for each condition separately. There are many ways to do this, but EXISTS is my choice, because it logically represents what you are trying to accomplish.

SELECT ca.Id, ca.Name 
FROM Car c
WHERE EXISTS (
    SELECT 1
    FROM CarSpec cs
    JOIN CarFeature cf on cf.Id = cs.CarFeatureid
    WHERE cs.CarId = c.Id
    AND cf.Feature = 'ForkThickness' AND cs.Value > 1.25
);

And

SELECT ca.Id, ca.Name 
FROM Car c
WHERE EXISTS (
    SELECT 1
    FROM CarSpec cs
    JOIN CarFeature cf on cf.Id = cs.CarFeatureid
    WHERE cs.CarId = c.Id
    AND cf.Feature = 'ForkThickness' AND cs.Value > 1.25
)
AND EXISTS (
    SELECT 1
    FROM CarSpec cs
    JOIN CarFeature cf on cf.Id = cs.CarFeatureid
    WHERE cs.CarId = c.Id
    AND cf.Feature = 'RearTires' AND cs.Value = 'Summer Tires'
);