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

• dhilditch

  SSC-Addicted

  Points: 495

  More actions

  August 18, 2004 at 4:44 pm

  #519660

  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: 246873

  More actions

  August 18, 2004 at 6:35 pm

  #519678

  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
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• brian7947

  Newbie

  Points: 2

  More actions

  August 18, 2004 at 6:42 pm

  #519679

  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

  August 19, 2004 at 3:17 am

  #519715

  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

  August 19, 2004 at 7:08 am

  #519757

  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

  August 19, 2004 at 7:15 pm

  #519921

  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

  August 19, 2004 at 9:11 pm

  #519934

  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.