如何SQL 1到多个关系?

I have two tables:

我有两个表:

一个

Aid: 1
Aid: 2
Aid: 3

Bid: 1 - Aid: 1 - qty: 2
Bid: 2 - Aid: 1 - qty: 2
Bid: 3 - Aid: 1 - qty: 5
Bid: 4 - Aid: 2 - qty: 3
Bid: 5 - Aid: 2 - qty: 2
Bid: 6 - Aid: 2 - qty: 2

How can I Sql query to get only row of table A that has Table B qty = 2?

如何使用Sql查询只获取表B qty = 2的表A的行?

The expected result is:

预期的结果是:

Aid: 1
Aid: 2

I've tried this:

我已经试过这个:

Select A.Aid FROM A INNER JOIN B ON A.Aid = B.Aid WHERE B.qty = 2

However, this gives me the result more than I need:

然而，这给了我超出我需要的结果:

Aid: 1
Aid: 1
Aid: 2
Aid: 2

Yes I don't care what's the Bid is. Any ideas?

是的，我不在乎出价是多少。什么好主意吗?

3 个解决方案

#1

You need to group by Aid

你需要通过援助来组织。

Select 
 A.Aid 
FROM A 
INNER JOIN B ON A.Aid = B.Aid 
WHERE B.qty = 2
GROUP BY A.Aid

Note: GROUP BY A.Aid ensures there will be at most one entry for each A.Aid

注:由一个组。援助确保每个援助最多有一个条目

EDIT:

编辑:

Using DISTINCT:

使用不同的:

SELECT
 DISTINCT A.Aid 
FROM A 
INNER JOIN B ON A.Aid = B.Aid 
WHERE B.qty = 2

Note: DISTINCT removes the duplicate rows in the final result set.

注意:DISTINCT将删除最终结果集中的重复行。

#2

In general the trick to writing SQL queries is to think of what you want to do in English, then try to rewrite it using SQL keywords.

一般来说，编写SQL查询的技巧是考虑用英语做什么，然后尝试使用SQL关键字重写它。

If I rewrite your sentence "get only row of table A that has Table B qty = 2" a little more SQL-y, you can say "select only row of table A where there exists a row in Table B with qty = 2 for that Aid".

如果我重写你的句子“只获取表B qty = 2的表A的行”，再多一点SQL-y，你可以说“选择表A中存在qty = 2的行作为辅助”。

So, you can use an correlated subquery with EXISTS:

因此，您可以使用存在的关联子查询:

select *
from A
where exists (
    select 1
    from B
    where B.Aid = A.Aid
    and B.qty = 2
)

#3

please try:

请尝试:

Select Distinct A.Aid FROM A INNER JOIN B ON A.Aid = B.Aid WHERE B.qty = 2

#1