Sql query needed

• bob mazzo

  SSCommitted

  Points: 1684

  More actions

  February 22, 2006 at 7:57 pm

  #67301

  hello,

  I'm trying to figure out how to write a certain query, but I'm not sure exactly what kind of join I need here.

  I have a Client table and columns "client" and "group_cx"

  Here's and example of the data:

  Client Group_cx

  Clnt001 Clnt005

  Clnt002

  Clnt003 Clnt005

  Clnt004

  Clnt005

  Clnt006 Clnt005

  Now I need to pull the "Client" column, but ONLY those records that contain the same Group_cx value. In this case I would pull Clnt001, Clnt003 and Clnt006 .

  What kind of query do I need here ?

  Thank you very much,

  Bob

• dimitris giannopoulos

  SSC Veteran

  Points: 265

  More actions

  February 23, 2006 at 1:46 am

  #622825

  Hi Bob

  Try the following...

  select distinct(a.client)/*,a.group_cx*/ from testtable a,testtable b

  where a.group_cx = b.group_cx

  order by /*a.group_cx,*/a.client

  The commented parts of the query are for you to check the correctness of your results

  I hope this helps

  Dimitris

• bob mazzo

  SSCommitted

  Points: 1684

  More actions

  February 23, 2006 at 8:16 am

  #622957

  Thank you for your suggestion. The distinct keyword was the key input there.

  Here's what finally worked for me (I forgot I needed to first match against the extrn_name column) ;

  select distinct(a.client) /*,a.group_cx*/ from client a, client b

  where a.group_cx in -- = b.client

  (select b.client from client b where b.extrn_name = 'DEMO')

  order by a.client

• dimitris giannopoulos

  SSC Veteran

  Points: 265

  More actions

  February 24, 2006 at 12:00 am

  #623122

  I 'm glad it helped you

  Best Regards