Vicheanak Vicheanak - 4 months ago 8
MySQL Question

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?

Answer

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.