Get specific records from DB

_Flaviu

I have the following tables and values:

t_cars

nCars_ID sName sModel sIdentifier

1 BMW 3 series D-78-JHG
2 Volvo C30 B-56-KHT
3 Fiat Doblo H-72-ABN
4 Volvo C40 J-78-YTR

t_feature

nFeature_ID sName

1 CMC
2 Doors
3 Color
4 Type
5 Weight
6 Engine
7 Power

t_cars_feature

nCarsFeature_ID nCars_ID nFeature_ID sValue

1 2 1 2500
2 2 2 5
3 2 4 Diesel
4 2 3 Green
5 3 1 1900
6 3 2 3
7 3 4 Otto
8 3 5 2300 KG
9 1 1 1900
10 1 3 Blue
11 1 4 Diesel
12 1 5 2100 KG

I need to retrieve from DB the cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY I have tried: SELECT t_cars.sName, t_cars.sModel, t_cars.sIdentifier FROM t_cars, t_feature, t_cars_feature WHERE t_feature.nFeature_ID = t_cars_feature.nFeature_ID AND t_cars.nCars_ID = t_cars_feature.nCars_ID AND [/*condition that get me cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY*/] I have tried the condition like that: Trial 1: AND t_feature.sName = 'CMC' AND t_feature.sName = 'Color' AND t_cars_feature.sValue = '1900' AND t_cars_feature.sValue = 'Blue' and get me nothing I have also tried: Trial 2: AND t_feature.sName IN ('CMC','Color') AND t_cars_feature.sValue IN ('1900','Blue') and get me all records that has CMC 1900 OR color 'Blue' (probably I got here cartesian product) In real situation I could have several t_feature.sName values, and several t_cars_feature.sValue values, that is why trial 1 are not suitable for me ... Can you help me ? Thank you.

A_Griffin

You need to select on the cars_features table more than once, so:

SELECT C.sName, C.sModel, C.sIdentifier
FROM t_cars AS C INNER JOIN t_cars_feature AS CF1 ON CF1.nCars_ID = C.nCars_ID
INNER JOIN t_cars_feature AS CF2 ON CF2.nCars_ID = C.nCars_ID
WHERE (CF1.nFeature_ID = 1 AND CF1.sValue = 1900)
AND (CF2.nFeature_ID = 3 AND CF2.sValue = 'Blue')

sName | sModel | sIdentifier ---------------------------- BMW | 3 series | D-78-JHG

Mycroft Holmes

First crate a query that selects the link records (t_cars_feature) you expect to see.

Select * from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...

Then change the query to only get the CarID

Select CarID from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...

Once you are happy with the results wrap the first query to select the cars

Select * from Cars where CarID in (insert query 1)

What you are missing is the heavy use of brackets. It is still an ugly database design.

Never underestimate the power of human stupidity RAH