SQL/Criteria Query CONTAINS ALL?

Question

Table A:        Table A_B:         Table B:
id | a          a_id | b_id        id | b
------          -----------        ------
1  | w          1    | 1           1  | s
2  | x          1    | 2           2  | t
3  | y          2    | 4           3  | u
4  | z          4    | 4           4  | v

Now I'd like to have ALL entries from table A WHERE B.id = 1 AND B.id = 2.

At the moment I have to following code:

SELECT *
FROM A a
JOIN A_B ab ON a.id    = ab.a_id
JOIN B b    ON ab.b_id = b.id

And here I got stuck. WHERE b.id IN (...) gives me all entries from Table A WHERE b.id = 1 OR b.id = 2of course, and WHERE b.id = 1 AND b.id = 2 gives no results at all...

The only possible solution I found is using INTERSECT:

SELECT *
FROM A a
JOIN A_B ab ON a.id    = ab.a_id
JOIN B b    ON ab.b_id = b.id
WHERE b.id = 1

INTERSECT

SELECT *
FROM A a
JOIN A_B ab ON a.id    = ab.a_id
JOIN B b    ON ab.b_id = b.id
WHERE b.id = 2

But I can have an infinite number of b.ids. So this query will become really slow...

Isn't there something like IN which behaves like I want? And it should be implemented using Criteria Query:

Join<A, B> aB = root.join(A_.bs); // as this is a @ManyToMany relationship
...

But I'd also be happy with a pure SQL solution.

i'm not sure I understand the question correctly but I think this is a case for left join http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html — Sam, Dec 10 '13 at 21:10
Your example would be clearer if you change the record in A_B from 2,4 to 2,2. Then indicate that record 2 in table A should not be returned despite matching the one value (because it doesn't match the other value). — mdahlman, Dec 10 '13 at 21:25

score 1 · Accepted Answer · answered Dec 10 '13 at 21:26

1

Tested on MySQL:

select TableA.id, count(*)
from tableA
join tableA_B on TableA.id=TableA_B.a_id
where b_id =1 or b_id=2
group by tableA.id
having count(*)=2

SQL Fiddle here.

answered Dec 10 '13 at 21:26

neutrino

2,297
4
20
28

1

This relies on counting... so it would return too many records if, for example, A_B contains the record 2,2 twice. – mdahlman Dec 10 '13 at 21:40

score 0 · Answer 2 · answered Dec 10 '13 at 21:13

0

I must admit I don't really follow the question and the problem, but are you saying that this doesn't work:

SELECT *
FROM B b
LEFT JOIN A_B ab ON b.id    = ab.b_id
LEFT JOIN A a    ON ab.a_id = a.id
WHERE b.id IN (1,2)

answered Dec 10 '13 at 21:13

Matt Runion

1,031
7
13

Read it again. I found a solution using INTERSECT. But your query just gives me results from Table A where b.id = 1 OR b.id = 2. But I want all entries from Table A where b.id = 1 AND b.id = 2. – Benjamin M Dec 10 '13 at 21:22

score 0 · Answer 3 · answered Dec 10 '13 at 21:22

As I understand it, this is a many-to-many relation, and you want to filter the data based on the Id on table B.

First, your first query seems ok to me; I would write it as follows for clarity:

select 
    a.id, a.a, b.id, b.b
from 
    A as a
    inner join A_B as ab on a.id = ab.a_id
    inner join B as b on ab.b_id = b.id;

Now, if you want all the records for which b.id = 1 or b.id = 2, this should work:

select 
    a.id, a.a, b.id, b.b
from 
    A as a
    inner join A_B as ab on a.id = ab.a_id
    inner join B as b on ab.b_id = b.id
where
    b.id in (1,2);

Check this SQLFiddle example

Hope this helps

Hey, thanks. I should have written a SQL fiddle... I modified yours: http://sqlfiddle.com/#!2/6628f/1 (Changed the entries in A_B table). The result should now only contains a.id = 1, because it's the only entry which matches b.id = 1 AND b.id = 2 — Benjamin M, Dec 10 '13 at 21:29

SQL/Criteria Query CONTAINS ALL?

3 Answers3