1

I have two tables. Table A and Table B. Both are connected with a many-to-many relationship.

Table A:

ID
---
1
2

Table B:

ID
---
3
4

Table AB:

ID | A_ID | B_ID
----------------
5  | 1    | 4
6  | 1    | 3
7  | 2    | 3

I want to get the list of IDs from table B which have a relation to a list of IDs of table A.

Example from the above tables:

I want to get all Bs which have a relation to table A ID 1 and ID 2. I get then ID 3 has to both IDs of table A.

How could I do this with an SQL query ?

wolfgangwalther
  • 1,226
  • 7
  • 15
Andreas Daoutis
  • 1,195
  • 8
  • 16
  • You want to have Bs that are related to only those As in the list or that are related to at least those As in the list, but possibly more? – wolfgangwalther Oct 30 '14 at 10:53
  • You are giving a list of As as input, right? This has been changed with the last edit, now reading ALL As... – wolfgangwalther Oct 30 '14 at 11:15
  • I would recommend this article about relational division: https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ – Horaciux Oct 31 '14 at 01:01

2 Answers2

2

If you are looking to select based on a list of As (not ALL As), then do it like this:

SELECT b_id
FROM ab
WHERE a_id IN (1,2)
GROUP BY b_id
HAVING COUNT(a_id) = 2

Replace (1,2) with your list and 2 in the having clause with the number of list items.

If you get your list of As from a subquery you could do it like that (not in MySQL, though...):

WITH subquery (
 --subquery code here
)

SELECT b_id
FROM ab
WHERE a_id IN subquery
GROUP BY b_id
HAVING COUNT(a_id) = (SELECT COUNT(*) FROM subquery)

In MySQL you would have to put your subquery code twice and drop the WITH clause.

You could also use a temporary table, which would then lead to selecting ALL As from that temporary table and thus Gordon Linoffs answer...

wolfgangwalther
  • 1,226
  • 7
  • 15
0

You can do this by joining and counting:

SELECT B_ID
FROM AB JOIN A 
         ON
     AB.A_ID = A.ID
GROUP BY AB.B_ID
HAVING COUNT(DISTINCT AB.A_ID) = (SELECT COUNT(distinct ID) FROM A);

If you know there are no duplicates in AB or A, you can remove the distinct from the count().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • ...and indeed the surrogate key in ab! – Strawberry Oct 30 '14 at 11:09
  • 1
    In the last month, you've acquired more rep than I've managed in 2 years. Are you on some kind of mission? ;-) – Strawberry Oct 30 '14 at 11:13
  • 1
    @Strawberry He is just intellectually generous and kind to help others. –  Oct 30 '14 at 11:18
  • Can anybody explain to me, why you are joining A here? That doesn't make any sense to me (yet). Is it just to eliminate entries in AB, that are not matched in A? – wolfgangwalther Oct 30 '14 at 11:46
  • @wolfgangwalther . . . It `join`s to `A` to get only the values in A. This is important for the `count()` in the `having` clause. – Gordon Linoff Oct 30 '14 at 12:01
  • Assuming that there are A_IDs in AB, that are not in A, right? But if AB.A_ID was a foreign key to A, which I would assume in this case, that would be no problem? – wolfgangwalther Oct 30 '14 at 12:04
  • @wolfgangwalther how can the query work without the `JOIN` for the second `COUNT` ? –  Oct 30 '14 at 12:18
  • @Begueradj The second `COUNT` is within a subquery. No need for the `JOIN` before that. – wolfgangwalther Oct 30 '14 at 12:19
  • @wolfgangwalther So you suggest the query will run successfully without the `JOIN` ? your remark is interesting, I do not see the difference if the query works directly on `AB` without A –  Oct 30 '14 at 12:25
  • 2
    @Begueradj . . . In many cases, you would not need the join on `A` in the inner query. In particular, if `A` is the reference table and if foreign key references are enforced. – Gordon Linoff Oct 30 '14 at 12:26
  • No, I rather up voted because, even without being able to test your query, it looks too logical to me even if it may not be too perfect. –  Oct 30 '14 at 16:23
  • @GordonLinoff probably because you didn't answer the original question, which asked for a list of ID's from A. Begueradj edited the question and changed the formatting and a lot of grammar/spelling etc. and accidently changed the meaning of that to "all ID's from A". – wolfgangwalther Oct 31 '14 at 00:21
  • But this actually happened after you answered the question anyways ;) – wolfgangwalther Oct 31 '14 at 00:22