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.

Answer 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.

Answer 2

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)

Answer 3

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