CODEWITHSUNDEEP
mysqlsql-server
Vicheanak
Vicheanak

Reputation: 6694

How to SQL One to Many Relationship?

I have two tables:

A

Aid: 1
Aid: 2
Aid: 3

B

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?

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?

Upvotes: 0

Views: 51

Answers (3)

1000111
1000111

Reputation: 13519

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.

Upvotes: 2

Grace
Grace

Reputation: 898

please try:

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

Upvotes: 0

lc.
lc.

Reputation: 116538

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.

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".

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
)

Upvotes: 2

Related Questions