Select objects from table A that is associated to all selected objects from Table B by SQL-CodePudding

Sorry for the long title but I have the following situation:

Table 1

id  |  title
---------------
1   |  object 1
2   |  object 2
3   |  object 3

Table 2

id  |  title
---------------
1   |  item 1
2   |  item 2
3   |  item 3

Table 3 (n-m association)

id  |  object_id  |  item_id
------------------------------
1   |  1          |  1
2   |  1          |  2
3   |  2          |  1
4   |  2          |  2
5   |  2          |  3
6   |  3          |  2
7   |  3          |  3

Now I want to get all objects from table 1 that a are associated to item 2 AND item 3.

The result should be object 2 and object 3.

But I have no clue how the SQL query should look like.

Any help?

CodePudding user response：

You can try something like this:

SELECT t.title
FROM Table1 t
WHERE EXISTS(SELECT 1 FROM Table3 s
             WHERE t.id = s.object_id
              AND s.item_id in(2,3)
             HAVING COUNT(*) = 2)

CodePudding user response：

Simple and straight-forward. You want t1 rows the ID of which is both related to item 1 and item 2:

select *
from t1
where id in (select object_id from t3 where item_id = (select id from t2 where title = 'item 1'))
  and id in (select object_id from t3 where item_id = (select id from t2 where title = 'item 2'));