Clever Geek Handbook

Get all records from table B that relate to multiple records (given list) from table A

I have two tables. Table A

and Table B

. Both are related to 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 a list ID

from a table B

that are relevant to the list of the ID

tableA

.

Example from the above tables:

I want to get all B

that are relevant to table A

ID

1 and ID

2. Then I get ID

3 for the ID

table A

.

How can I do this with a SQL query?

+1


source to share


2 answers


If you want to select based on the list As (not ALL As), follow these steps:

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

Replace with (1,2)

your list and 2

in the having clause with the number of elements in the list.

If you get your list of As from a subquery, you can do it like this (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 will have to insert the subquery code twice and omit the WITH clause.

You can also use a temporary table, which will then cause ALL As to be selected from that temporary table, and thus Gordon Linoffs will answer ...

+2


source


You can do this by attaching 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 what duplicates are in AB

or A

not, you can delete distinct

from count()

.

0


source






More articles:


All Articles