开发者

How do I use SQL to select rows that have > 50 related rows in another table?

开发者 https://www.devze.com 2023-01-19 17:34 出处:网络
I\'ve been trying to find out how to write this query in sql. What I need is to find the productnames (in the products table) that have 50 or more orders (which are in the order table).

I've been trying to find out how to write this query in sql.

What I need is to find the productnames (in the products table) that have 50 or more orders (which are in the order table).

only one orderid is matched up to a productname at a time so when I try to count the orderid's it counts all of them.

I can get distinct productnames but once i add in the orderid's then it goes back to having multiple productnames.

I also need to count the number of customers (in the order table) that have ordered those products.

I need some serious help ASAP! if anyone could help me figure out how to figure this out that would be awesome!

Table: Products
      `produ开发者_JS百科ctname` in the form of a text like 'GrannySmith'

Table: Orders
      `orderid` in the form of '10222'..etc
      `custid` in the form of something like 'SMITH'


Assuming the orders table has a field that relates back to the products table named ProductId. The SQL would translate to:

SELECT p.ProductName, Count(*)
FROM Orders o 
JOIN Products p
  on o.ProductId = p.ProductId
GROUP BY p.ProductName HAVING COUNT(*) >= 50

The key is in the having component of the Group By clause. I hope this helps.


You might be missing an "Order Details" table - typically, an order has several order details, and each of the order details then maps to a product - something like the sample in Northwind:

How do I use SQL to select rows that have > 50 related rows in another table?

In that case, your SQL query would be something like this: join the [Order Details] table to both the [Orders] and [Products] tables, group by the product ID and name, and count the OrderID's:

select 
    p.ProductID, p.ProductName, count(o.OrderID)
from
    [order details] od
inner join
    orders o on od.OrderID = o.OrderID
inner join
    products p ON od.productID = p.ProductID
group by
    p.ProductID, p.ProductName
having
    count(o.OrderID) > 50
0

精彩评论

暂无评论...
验证码 换一张
取 消