从表B获取所有与表A的多个条目(给定列表)相关的条目 [英] Get all entries from Table B which have a relation to multiple entries (given list) from Table A

问题描述

我有两个桌子.表A和表B.两者都具有多对多关系.

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

表A:

ID
---
1
2

表B:

ID
---
3
4

表AB:

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

我想从表B中获取ID的列表，该列表与表A 中的ID的列表有关.

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

上表中的示例:

我想获取与表A ID 1和ID 2有关系的所有B.然后，我得到ID 3必须与表A的两个ID有关.

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.

如何使用SQL查询来做到这一点?

How could I do this with an SQL query ?

推荐答案

如果要基于As(而不是ALL As)列表进行选择，请按照以下步骤进行操作:

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

将(1,2)替换为列表，将2替换为hading子句中的列表项.

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

如果您从子查询中获得了As的列表，则可以这样做(尽管不是在MySQL中...):

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)

在MySQL中，您必须将子查询代码放入两次，并删除WITH子句.

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

您还可以使用一个临时表，这将导致从该临时表中选择ALL As，因此Gordon Linoffs会回答...

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

这篇关于从表B获取所有与表A的多个条目(给定列表)相关的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！