Reputation: 5836
Trying so select some rows from a joined table where there are multiple relevant rows.
This is a very simplified version of the original query, so please no suggestions on the general table structure.
Table A
id, title
------------
1, housea
2, houseb
3, housec
4, housed
Table B
id, cid, attrib, val
--------------------
1, 1, bathrooms, 2
2, 1, bedrooms, 1
3, 2, bathrooms, 0
4, 1, pools, 1
5, 2, bedrooms, 1
6, 2, pools, 1
7, 3, bathrooms, 1
8, 4, bathrooms, 1
9, 4, bedrooms, 1
Select all objects which have at least one bathroom and one bedrooms.
So as result only these two should appear:
2, housea
4, housed
This is not working:
SELECT a.id, title
FROM tablea a
LEFT JOIN tableb b ON b.cid = a.id
WHERE (b.attrib = "bathrooms" AND b.val > 0) AND (b.attrib = "bedrooms" AND b.val > 0)
nor is this:
SELECT a.id, title
FROM tablea a
LEFT JOIN tableb b1 ON b1.cid = a.id AND (b1.attrib = "bathrooms" AND b1.val > 0)
LEFT JOIN tableb b2 ON b2.cid = a.id AND (b2.attrib = "bedrooms" AND b2.val > 0)
Thanks for your suggestions!
Upvotes: 0
Views: 58
Reputation: 561
Try running Correlated subquery as given below:
select a.id, a.title from tablea a where 2 >=
(select count(b.cid) from tableb b where b.cid = a.id and b.attrib in ('bathrooms' , 'bedrooms') and b.val > 0 group by b.cid)
Upvotes: 0
Reputation: 1271241
Your second version is basically correct, except you want inner joins:
SELECT a.id, a.title
FROM tablea a JOIN
tableb b1
ON b1.cid = a.id AND (b1.attrib = 'bathrooms' AND b1.val > 0) JOIN
tableb b2
ON b2.cid = a.id AND (b2.attrib = 'bedrooms' AND b2.val > 0) ;
All your filtering is in the ON
clauses, so no rows are actually being filtered out. You would see the filtering if you looked at columns from b1
or b2
.
Upvotes: 1