sql help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

sql help

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-09-21 : 15:32:14

Hello,

I'm still getting familiar with joins and needed some help.

I have the following tables:

customers: customerid [pk], name, address, email
products: productid [pk], name, price
orders: orderid [pk], customerid [fk], ordertotal, orderdate
orderitems: orderitemid [pk], orderid [fk], productid [fk], orderitemqty

How would I write the sql to return all products that have been bought more than 5 times?

Thanks for any help.
Regards,
-D-

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-21 : 15:48:26

Try this:

SELECT p.name, COUNT(*)
FROM products p
INNER JOIN orderitems oi
ON p.productid = oi.orderitemid
INNER JOIN orders o
ON oi.orderid = o.orderid
GROUP BY p.name
HAVING COUNT(*) >= 5

Tara

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-09-21 : 16:23:57

Thank you Tara...I appreciate your help.

Regards,
-D-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources