Gaurav Srivastava Gaurav Srivastava - 4 months ago 9
MySQL Question

MYSQL query for one to many relation

i have two tables:-

1- section

id name location_id
1 demo1 20
2 demo2 34


2- amenities

id amenity_id amenity_type object_id object_type

1 wedding_hall venue_type 1 section

2 conference_hall venue_type 1 section

3 conference_hall venue_type 2 section


i want all those section whose location_id is 134 and who has wedding_hall and conference hall both. I have tried this query:-

SELECT s.* from section s
INNER JOIN amenities am
on (am.object_type='section' AND am.object_id=s.id)
WHERE s.location_id=134 AND
(am.amenity_type LIKE 'venue_type' and am.amenity_id='wedding_hall')
AND (am.amenity_type LIKE 'venue_type' and am.amenity_id='conference_hall')


if i do this query:-

SELECT s.* from section s
INNER JOIN amenities am
on (am.object_type='section' AND am.object_id=s.id)
WHERE s.location_id=134 AND
(am.amenity_type LIKE 'venue_type' and am.amenity_id='wedding_hall')


then it works bot not for more than one amenity.
here is sqlfiddle

how can i correct my query?

Answer

If I did not mistake your question and you really want section which id = 1, try this:

SELECT s.*
FROM section s
INNER JOIN amenities am
ON am.object_type='section' AND am.object_id=s.id
WHERE s.location_id=134
AND am.amenity_type LIKE 'venue_type'
AND am.amenity_id IN ('wedding_hall', 'conference_hall')
-- HAVING COUNT(DISTINCT am.amenity_id) > 1
GROUP BY s.id
HAVING COUNT(DISTINCT am.amenity_id) = 2

SQLFiddle Demo

Comments