Query help - only 1 item and only 2 items bought

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query help - only 1 item and only 2 items bought

Author

Topic

C3000
Starting Member

3 Posts

Posted - 2007-07-30 : 02:04:26

I'm trying to create a query that returns customers who ordered only 1 product, and another for only 2 products. For example if I had 2 tables tblCustomer(ID, Name) and tblOrder(OrderNbr,Product). Any thoughts?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 02:46:34

Yes.

1) INNER JOIN
2) HAVING
3) COUNT

But before all these, how do you know which orders belongs to which customer?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 02:49:12

[code]SELECT c.ID,
c.Name
FROM tblCustomer AS c
INNER JOIN tblOrder AS o ON o.CustomerID = c.ID
GROUP BY c.ID,
c.Name
HAVING COUNT(*) = 1 -- 2 if you want customers with exactly 2 orders
ORDER BY c.Name,
c.ID DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

C3000
Starting Member

3 Posts

Posted - 2007-07-30 : 23:17:07

Thanks that got me on the right track. My next question is if I wanted all customers that only bought 1 type of product. For example, all customers that bought any number of apples, but nothing else. Would that go in the WHERE or HAVING clause? tblC(ID,name) and tblO(OrderNbr,Product,ID)etc. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 02:09:34

HAVING COUNT(DISTINCT ProductType) = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources