Three total tables, count number of records from tables 1 and 2 that are connected to table 3

Question

I have three tables, Tb_Requests, TB_Offers, and Tb_Product.

TB_Offers: Prod_ID

Tb_Requests: Prod_ID

Tb_Product: Prod_ID Name

I need to list the Name from Tb_Product with the count from Tb_Requests and TB_Offers that match Tb_Product in one query.

This is what I have so far:

select p.[Name], count(o.Supp_ID) '# of Offers', count(r.Con_ID) '# of Requests' from Tb_Requests r, TB_Offers o, Tb_Product p
where r.Prod_ID = o.Prod_ID and o.Prod_ID = p.Prod_ID
group by p.[Name]

Results:
Name | # of Offers | # of Requests

• Airplane |6 |6
• Auto | 25 |25
• Computer |72 |72
• Milk | 16 |16
• Oil | 20 |20
• Orange | 36 |36
• Truck | 6 |6
• TV | 20 |20

but my results should be:
NAME | # of Offers | # of Requests

---

• Airplane | 6 | 1
• Auto | 5 | 5
• Computer | 12 | 6
• Milk | 4 | 4
• Oil | 4 | 5
• Orange | 6 | 6
• Truck | 6 | 1
• TV | 4 | 5

(8 row(s) affected)

I need to combine these two queries:

select p.[Name], count(o.Supp_ID) '# of Offers'
from TB_Offers o, Tb_Product p
where p.Prod_ID = o.Prod_ID
group by p.[Name]

select p.[Name], count(r.Con_ID) '# of Requests'
from Tb_Requests r, Tb_Product p
where p.Prod_ID = r.Prod_ID
group by p.[Name]

Any help would be appreciated.

Answer 1

The numbers you are seeing is because of the number of rows in each table - for "Oil" there are 4 rows in Offers and 5 and Requests, giving you 20 rows total.

There are a few options here. You can re-write your query to use outer apply to each of the Offers and Requests tables, giving you a distinct aggregate figure for each to join to. You don't specify your RDMS, but you likely can also try count(distinct ) to give you a distinct count as well.

There are some good resources to read more about this - try: Why do multiple-table joins produce duplicate rows?