Timo002 Timo002 - 4 days ago 5
MySQL Question

MySQL join tables where all left records are in right

I need to get matching records from table A that are also in table B. But I can't figure out if I can do this in just one query.

Table A

id | order_id | title
----------------------------------
1 | 1114 | Product 1
2 | 1115 | Product 1
3 | 1115 | Product 2
4 | 1116 | Product 1
5 | 1117 | Product 3
6 | 1118 | Product 4
7 | 1118 | Product 5


Table B

id | order_id | a_id | title
----------------------------------
1 | 2221 | 1 | Product 1
2 | 2222 | 2 | Product 1
3 | 2222 | 3 | Product 2
4 | 2223 | 7 | Product 5


Now I need all
order_id
values from table A where all products are also in table B. So this would result in:

1114
1115


1116 fails because there is no
b.a_id
value
4


1117 fails because there is no
b.a_id
value
5


1118 fails because not both
6
AND
7
are in
b.a_id


I tried an
INNER JOIN
, but this would also return
1118
. I also tried a
LEFT JOIN
with
WHERE b.a_id IS NULL
, but that is the exact opposite. So I tried a
RIGHT JOIN
, but that also returns
1118
.

See this SQL Fiddle.

I hope I made clear what my goal is and I'm curious if this could be managed by one query.

Answer

It's not perfect, but it does return the correct results for your existing data through the SQL Fiddle. Maybe you can use this as a starting point?

SELECT a.order_id, count(distinct a.id) as aCount, count(distinct b.a_id) as bCount
FROM a
LEFT JOIN b ON a.id = b.a_id
GROUP BY a.order_id
HAVING aCount = bCount;
Comments