I have two tables. Table A and Table B. Both are connected with a many-to-many relationship.
Table A:
ID
---
1
2
3
Table B:
ID
---
4
5
6
7
Table AB:
ID | A_ID | B_ID
----------------
8 | 1 | 4
9 | 1 | 5
10 | 1 | 6
11 | 1 | 7
12 | 2 | 5
13 | 2 | 6
14 | 3 | 6
I want to find all Ids from table A, which have assigned Ids from table B from only given B Ids. For example here, we pass Ids in our queries - [5,6] So it should return all A ids which have either all values from array [5,6] assigned or any no. of values but only from array. If other than array id is also assigned then don't include in result. Here result will be 2 and 3. ( 1 is not because it also has 4,7 assigned to it). I am using Sequelize with typescript.
You need a sub select to omit unwanted A_ID. Playground: http://sqlfiddle.com/#!9/524468/3
SELECT DISTINCT A_ID
FROM AB
WHERE B_ID IN (5, 6)
AND A_ID NOT IN (
SELECT DISTINCT A_ID
FROM AB
WHERE B_ID NOT IN (5, 6)
)