Help With a query

Question

Post reply

Help With a query

brian7947

Newbie

Points: 2
More actions
August 18, 2004 at 2:47 pm

#161999
Hello,
I am trying to retrieve distinct ClientID's that match more than 1 item for example my query is currently
Code:
Select distinct ClientID from ClientRegs where RegionID = 1 and RegionID = 28
The Table structure example is
ClientID RegionID
222         1
222        28
444         1
444        44
So I want the above query to return only 222 because it is the only client id that has a record for RegionID 1 and 28.
The above query doesn't work because the RegionID column will never be both 1 and 28 on the same record.
I've also tried
Code:
Select distinct ClientID from ClientRegs where RegionID = 1 or RegionID = 28
This query does not work because it will return 444 and 222.
Does anyone know if what I'm trying to do is possible and how it would be done?
Thanks,
Brian

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dhilditch SSC-Addicted Points: 495 More actions · Answer 1

Here's a couple of methods:

create table clients (clientid int, regionid int)

insert into clients

select 222,1

union

select 222,28

union

select 444,1

union

select 444,44

select clientid from

clients

where regionid = 28

and clientid in

(

select clientid from clients where regionid = 1

)

select clients.clientid

from clients

inner join

(

select clientid from clients where regionid = 1

) x

on clients.clientid = x.clientid

and regionid = 28

If however, you might have any number of regionid's then you're going to need to use dynamic sql. Let me know and I'll post you a version with dynamic sql - not very fast though.

Dave Hilditch.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

Here's another version that does not require any joins:

Select ClientID from ClientRegs

where RegionID = 1 or RegionID = 28

group by ClientID

having count(ClientID) > 1

Regards

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

brian7947 Newbie Points: 2 More actions · Answer 3

Thanks to everyone that posted. I used

select clientid from

clients

where regionid = 28

and clientid in

(

select clientid from clients where regionid = 1

)

and it works

Enthusiast it seems your suggestion is a simple solution I may try it although I have already wrote the code using the other statement.

Thanks For all the information.

AKM SSCommitted Points: 1538 More actions · Answer 4

I like this method! My usual solution to such requirements is to use selfjoins or EXISTS subqueries, but I think in future, at least when doing quick-n-dirty stuff in QA, I will use this method. If we switch to using IN for the (here) RegionID list, this is the quickest method (in typing terms ) to extend:

select ID from Thing where OtherID IN ( 3,4,5 ) -- just type new ones here
group by ID having count(ID) > 2 -- and make sure this is the number of OtherIDs to match, less one

Mike Serrano Newbie Points: 1 More actions · Answer 5

TRY THIS:

SELECT ClientID FROM ClientRegs WHERE RegionID=1 AND ClientID in

(SELECT ClientID FROM ClientRegs WHERE RegionID='2')

IT WORK FINE FORM ME.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

Yeah, I usually use a self-join for something like this. I tested it out, though, and using the group by costs half of using the self-join.

Nice technique! I would not have expected this result.

create table #clients (clientid int, regionid int)

insert into #clients

select 222,1

union

select 222,28

union

select 444,1

union

select 444,44

--cost = .0931

select c1.ClientID

From #Clients c1

JOIN #Clients c2 on c1.ClientID = c2.ClientID

where c1.RegionID = 1 and c2.RegionID = 28

--cost = .0490

Select ClientID

from #Clients

where RegionID in( 1, 28)

group by ClientID

having count(ClientID) > 1

Signature is NULL

dhilditch SSC-Addicted Points: 495 More actions · Answer 7

If you add a clustered index to the table though....

create clustered index idx_foo on #clients (clientid)

--cost = .0443

select c1.ClientID

From #Clients c1

JOIN #Clients c2 on c1.ClientID = c2.ClientID

where c1.RegionID = 1 and c2.RegionID = 28

--cost = .0376

Select ClientID

from #Clients

where RegionID in( 1, 28)

group by ClientID

having count(ClientID) > 1

Which should mean that the group by is still quicker if you trust Execution Plan cost estimation as a means of measuring the speed something will run at. It's really just a cost for internal use - for true time estimates you should look at the statistics provided by the server.

I ran the statistics and it looks like the group by is still quicker though.

Dave Hilditch.