help with select statement - 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

General SQL Server Forums

New to SQL Server Programming

help with select statement

Author

Topic

Buzz
Starting Member

2 Posts

Posted - 2010-06-21 : 14:35:31

Hi, I have been spining my wheels trying to figure out what should be a simple soloution. So instead of sitting here trying the same things over and over I figured I should just ask someone for help lol. Anyway here is the issue I am having...

I have a table with columns RepID, CustomerID, SaleType, Office, etc

now each Rep can have multilpe Customers which could have multilpe salestypes. I want to return the customerID if saletype is "Books" and "Pencils"

RepID --- CustomerID --- SaleType --- Office
1 ------- abc ------- Books ------- CANADA
1 ------- abc ------- Pencils ------- USA
1 ------- efg ------- Books ------- CANADA
2 ------- hij ------- Books ------- USA
2 ------- hij ------- bags ------- USA

Thank you for you time and much needed help

Kristen
Test

22859 Posts

Posted - 2010-06-21 : 14:53:21

[code]
SELECT DISTINCT T1.CustomerID
FROM MyTable AS T1
JOIN MyTable AS T2
ON T2.CustomerID = T1.CustomerID
AND T2.SaleType = 'Pencils'
WHERE T1.SaleType = 'Books'
[/code]
would work for two categories, not very scalable if you want "n" categories.

It would be better if you moved SaleType out of that table into a Child Table instead ("normalisation")

Amber_Deslaurier
Starting Member

40 Posts

Posted - 2010-06-21 : 19:46:59

I think this is what you are looking for...

Select

CustomerID,
SaleType

FROM YOURTABLE

WHERE SaleType IN (Books, Pencils) ' If you want either one

Kristen
Test

22859 Posts

Posted - 2010-06-22 : 03:53:18

quote:
Originally posted by Amber_Deslaurier
If you want either one

"if saletype is "Books" and "Pencils"" suggests both to me, rather than either - but the O/P can clarify

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-22 : 04:46:39

select customerid from table
where saletype in ('Books' , 'Pencils')
group by customerid
having count(distinct saletype )=2

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-22 : 06:54:25

I knew there was a slicker way!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-22 : 08:24:38

quote:
Originally posted by Kristen

I knew there was a slicker way!

Madhivanan

Failing to plan is Planning to fail

Buzz
Starting Member

2 Posts

Posted - 2010-06-22 : 11:14:28

quote:
Originally posted by madhivanan

select customerid from table
where saletype in ('Books' , 'Pencils')
group by customerid
having count(distinct saletype )=2

Madhivanan

Failing to plan is Planning to fail

Thank you, that works prefectly!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-23 : 02:51:26

quote:
Originally posted by Buzz

quote:
Originally posted by madhivanan

select customerid from table
where saletype in ('Books' , 'Pencils')
group by customerid
having count(distinct saletype )=2

Madhivanan

Failing to plan is Planning to fail

Thank you, that works prefectly!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources