Hà Nam Anh Phạm Hà Nam Anh Phạm - 1 month ago 5
SQL Question

Selecting Properties from Property_Features list

I have two SQL tables:

PROPERTY
PID Address
1 123 Center Road
2 23 North Road
3 3a/34 Crest Avenue
5 49 Large Road
6 2 Kingston Way
7 4/232 Center Road
8 2/19 Ash Grove
9 54 Vintage Street
10 15 Charming Street

PROPERTY_FEATURE
P.PID Feature
1 Wine Cellar
1 Helipad
2 Tennis Court
2 Showroom
7 Swimming Pool - Above Ground
9 Swimming Pool - Below Ground
9 Wine Cellar


I want to Select the properties which contains specific features. For example, I would like to select the property ID which has the features Wine Cellar and Helipad, it would return the Property with the ID of 1.

Any ideas?

Answer

You can do this using Group By and Having clause

select PID 
From PROPERTY_FEATURE
Group by PID 
Having COUNT(case when Feature = 'Wine Cellar' then 1 end) > 0 --1
   and COUNT(case when Feature = 'Helipad' then 1 end) > 0 -- 2

1 ) Counts only when Feature = 'Wine Cellar' & > 0 will make sure atleast one 'Wine Cellar' exist for each PID

2) Counts only when Feature = 'Helipad' & > 0 will make sure atleast one 'Helipad' exist for each PID

AND will make sure both 1 & 2 is satisfied then return the PID

Comments