SQL Server SQL使用table2和table3的计数获取table1名称

t9eec4r0 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(147)

I have three tables, table1 is connected to table2 and table3, but table2 and table3 are not connected. I need an output count of table2 and table3 for each table1 row. I have to use joins and a group by table1.name

SELECT Tb_Product.Name, count(TB_Offers.Prod_ID) 'Number of Offers', count(Tb_Requests.Prod_ID) 'Number of Requests'
FROM Tb_Product LEFT OUTER JOIN
                         Tb_Requests ON Tb_Product.Prod_ID = Tb_Requests.Prod_ID LEFT OUTER JOIN
                         TB_Offers ON Tb_Product.Prod_ID = TB_Offers.Prod_ID
GROUP BY Tb_Product.Name

I need to combine these queries:

SELECT Tb_Product.[Name], count(TB_Offers.Prod_ID) 'Number of Offers'
FROM Tb_Product  LEFT OUTER JOIN
                         TB_Offers ON Tb_Product.Prod_ID = TB_Offers.Prod_ID
GROUP BY Tb_Product.[Name]

SELECT Tb_Product.[Name], count(Tb_Requests.Prod_ID) 'Number of Requests'
FROM Tb_Product LEFT OUTER JOIN
                         Tb_Requests ON Tb_Product.Prod_ID = Tb_Requests.Prod_ID
GROUP BY Tb_Product.[Name]

Results:

Name    Number of Offers
Airplane    6
Auto    5
Bike    3
Camera  0
Computer    12
Milk    4
Oil 4
Orange  6
Telephone   0
Truck   6
TV  4

Name    Number of Requests
Airplane    1
Auto    5
Bike    0
Camera  2
Computer    6
Milk    4
Oil 5
Orange  6
Telephone   0
Truck   1
TV  5

My results for offers and requests are the same value. I am not sure what I am doing wrong with the joins. Do I need to somehow join product to request and separately join product to offers? This needs to be done in one query.
This is for a class. Explanation would also be appreciated.

sql-server

来源：https://stackoverflow.com/questions/74719253/sql-get-table1-names-with-a-count-of-table2-and-table3

1条答案

按热度按时间

flvlnr441#

The simplest way to do this is to count the distinct values of each column:

SELECT 
    Tb_Product.Name, 
    count(distinct TB_Offers.Prod_ID) 'Number of Offers', 
    count(distinct Tb_Requests.Prod_ID) 'Number of Requests'
FROM 
    Tb_Product 
    LEFT OUTER JOIN
    Tb_Requests ON Tb_Product.Prod_ID = Tb_Requests.Prod_ID 
    LEFT OUTER JOIN
    TB_Offers ON Tb_Product.Prod_ID = TB_Offers.Prod_ID
GROUP BY 
    Tb_Product.Name

This is necessary because of the way joins work consecutively to produce a rowset that is a combination of all the input relations. COUNT() normally performs a count of non-null values in a column.
You can also do something like this, which aggregates the counts from the child tables independently and then joins them to the base table:

SELECT
    p.Name,
    o.cnt as Offer_Count,
    r.cnt as Request_Count
FROM 
    TB_Product p
    LEFT OUTER JOIN 
    (SELECT Prod_ID, COUNT(1) cnt FROM TB_Offers GROUP BY Prod_ID) o 
    LEFT OUTER JOIN 
    (SELECT Prod_ID, COUNT(1) cnt FROM TB_Requests GROUP BY Prod_ID) r

More explanation...
Let's say you have two products:
| Prod_ID | Name |
| ------------ | ------------ |
| 1 | Widget |
| 2 | Gizmo |
And two offers, one for each product:
| Offer_ID | Prod_ID |
| ------------ | ------------ |
| 100 | 1 |
| 200 | 2 |
And two requests for each product:
| Request_ID | Prod_ID |
| ------------ | ------------ |
| 1001 | 1 |
| 1002 | 1 |
| 2001 | 2 |
| 2002 | 2 |
Now you join Product relation to Offer relation on Prod_ID, you get a result like this:
| Prod_ID | Name | Offer_ID | Prod_ID |
| ------------ | ------------ | ------------ | ------------ |
| 1 | Widget | 100 | 1 |
| 2 | Gizmo | 200 | 2 |
Now when you join that relation to Requests on Prod_ID, you get something like this:
| Prod_ID | Name | Offer_ID | Prod_ID | Request_ID | Prod_ID |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | Widget | 100 | 1 | 1001 | 1 |
| 1 | Widget | 100 | 1 | 1002 | 1 |
| 2 | Gizmo | 200 | 2 | 2001 | 2 |
| 2 | Gizmo | 200 | 2 | 2002 | 2 |
Now when you count any of these columns you get 4 because each column has 4 values.

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server SQL使用table2和table3的计数获取table1名称

1条答案

相关问题

热门标签

最新问答