In List Query 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

General SQL Server Forums

New to SQL Server Programming

In List Query Help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-10-20 : 12:55:32

I want to get all the customernames from a table CustomerInfo which are in 2 regions A and B

My SQL Query

Select CustomerName,Region from CustomerInfo where Region in ('A','B')

With the above query iam getting all the customer names which are in A or B but i want customers who are in both A and B

Thanks in advance
Jim

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-20 : 13:17:38

select t1.CustomerName from
(select CustomerName from CustomerInfo where Region='A') as t1
inner join (select CustomerName from CustomerInfo where Region='B') as t2
on t1.CustomerName=t2.CustomerName

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-20 : 14:44:41

Depending on your data you might be able to make use of the HAVING clause:

Select 
    CustomerName
from 
    CustomerInfo 
where 
    Region in ('A','B')
group by
    CustomerName
having 
    COUNT(*) = 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 00:42:24

[code]
Select
CustomerName
from
CustomerInfo
where
Region in ('A','B')
group by
CustomerName
having
COUNT(distinct Region) = 2
[/code]
if you've multiple records per same region

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-10-21 : 04:35:06

What if the customer is in another region too? Do you want to exclude in that case?

Madhivanan

Failing to plan is Planning to fail

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-10-21 : 09:46:45

nope.i do not want to exclude a customer in another region

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 09:53:06

quote:
Originally posted by jim_jim

nope.i do not want to exclude a customer in another region

then my suggestion should be sufficient

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-10-21 : 11:07:57

okay.Thanks Everyone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 11:46:55

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources