CODEWITHSUNDEEP
mysqldatabasejoin
ranjith
ranjith

Reputation: 209

Mysql query to filter result

I have 2 tables 

TBL 1: property               TBL 2: property_detail
---------------------         --------------------------------------
Id   Name   status            property_id   param       value
---------------------         --------------------------------------
1    X       1                  1          parking      two-wheeler 
2    Y       1                  1          furnishing   furnished  
3    Z       0                  2          parking      car-parking 
4    A       1                  2          furnishing   semi-furnished
5    B       1                  3          furnishing   furnished 
6    C       0                  4          parking      car-parking

"property_id" column in "property_detail" is foreign key of "Id" column in "property"

I want search result for status=1 and (param="parking" and value="car-parking") and (param="furnishing" and value="furnished")

From above Example table, the result will be 

Result
-------------
id   name
-------------
2     Y

How to achieve this?

Upvotes: 1

Views: 43

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

you can get your desired result set by using join twice with details table

select p.*
from property p
join property_detail d on (p.Id = d.property_id)
join property_detail d1 on (p.Id = d1.property_id)
where p.status=1 
and d.param='parking' and d.value='car-parking'
and d1.param='furnishing' and d1.value='semi-furnished';

Another way you can also use below query using having clause and sum function 

select p.*
from property p
join property_detail d on (p.Id = d.property_id)
where p.status=1 
group by p.Id
having sum(param="parking" and value="car-parking") 
and sum(param="furnishing" and value="semi-furnished")

DEMO

Upvotes: 2

Sam Teng Wong
Sam Teng Wong

Reputation: 2439

 SELECT property.id, property.name 
 FROM property INNER JOIN property_detail 
 ON property.id = property_detail.property_id 
 WHERE 
 (`param`="parking" AND `value`="car-parking") 
 AND 
 (`param`="furnishing" AND `value`="furnished")
 AND status = 1;

can you try this one? I'm not sure though.. but it'll give you an idea.

Upvotes: 0

Related Questions