CODEWITHSUNDEEP
mysqlsqljoin
Mike
Mike

Reputation: 5836

MySQL: Joining a table where multiple rows are part of the condition

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

Answers (2)

Sonal Borkar
Sonal Borkar

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

Gordon Linoff
Gordon Linoff

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

Related Questions