CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Flaviu_
Flaviu_

Reputation: 1346

Get specific records from DB

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 an 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.

Upvotes: 1

Views: 57

Answers (3)

Ido Gal
Ido Gal

Reputation: 527

Use this query:

SELECT *
FROM   t_cars        
WHERE EXISTS (SELECT * FROM t_cars_feature AS cf 
                       JOIN t_feature AS f ON (f.nFeature_ID = cf.nFeature_ID)
              WHERE t_cars.nCars_ID = cf.nCars_ID AND f.sName = 'CMC' AND cf.sValue = '1900')     
      AND 
      EXISTS (SELECT * FROM t_cars_feature AS cf 
                       JOIN t_feature AS f ON (f.nFeature_ID = cf.nFeature_ID)
              WHERE t_cars.nCars_ID = cf.nCars_ID AND f.sName = 'Color' AND cf.sValue = 'Blue');

See the full example here.

I would like to comment about some of the things you've done there:

  • It is generally considered a bad practice to notate the type as a prefix in the column name, or in the object's name.
  • Your usage of the IN clause is wrong. I suggest you to try and practice about this matter a bit. When you supply a list of values inside the IN clause, it means that any of those values should result in a true value for the predicate - not that both of the them should exists.
  • Please note that when you use the WHERE clause with multiple predicates separated by an "AND", you require all of them to exists in a SINGLE row. Check out the result of your JOIN and see if this is the case to understand the case better.
  • It is considered a better practice to use a JOIN (i.e. INNER JOIN) and not a cartesian product (,) - even though both queries will usually compile into the same execution plan. It is just more readable and understandable.

Upvotes: 3

Zorkolot
Zorkolot

Reputation: 2027

You could do it with EXISTS and NOT EXISTS:

--Alias = Table
--    C = t_cars
--   CF = t_cars_feature
--    F = t_feature

SELECT * 
  FROM t_cars C
 WHERE EXISTS (SELECT *
                 FROM t_cars_feature CF INNER JOIN t_feature F ON CF.nFeature_ID = F.nFeature_ID
                WHERE CF.nCars_ID = C.nCars_ID --matches t_cars ID 
                  AND F.sName = 'CMC' --has CMC feature
                  AND F.sName = 'Color' --has color feature
                  AND CF.sValue = '1900'  --CMC = 1900 
                  AND CF.sValue = 'Blue'  --Color = 'Blue'
              )
   AND NOT EXISTS 
              (SELECT *
                 FROM t_cars_feature CF INNER JOIN t_feature F ON CF.nFeature_ID = F.nFeature_ID
                WHERE CF.nCars_ID = C.nCars_ID --matches t_cars ID 
                  AND F.sName = 'CMC' --has CMC feature
                  AND F.sName = 'Color' --has color feature
                  AND CF.sValue = '1900'  --CMC = 1900 
                  AND CF.sValue <> 'Blue'  --Color = 'Blue ONLY'
              )

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can make some use of conditional aggregation to get the desired results

SELECT t_cars.sName, t_cars.sModel, t_cars.sIdentifier 
FROM t_cars
JOIN t_cars_feature  ON t_cars.nCars_ID = t_cars_feature.nCars_ID 
JOIN t_feature ON t_feature.nFeature_ID = t_cars_feature.nFeature_ID 
WHERE t_feature.sName IN ('CMC','Color')
GROUP BY t_cars.sName, t_cars.sModel, t_cars.sIdentifier
HAVING SUM(CASE WHEN t_feature.sName ='CMC' AND t_cars_feature.sValue ='1900' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN t_feature.sName ='Color' AND t_cars_feature.sValue ='Blue' THEN 1 ELSE 0 END) >0

Demo

Upvotes: 0

Related Questions