Sql Query needed please

Last Post 23 Feb 2006 05:48 AM by robertmazzo. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
robertmazzo
New Member
New Member

--
22 Feb 2006 06:32 PM
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 with columns "client" and "group_cx". I need to pull the values for the client column that contain the same Group_cx value.

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
mwesch
New Member
New Member

--
22 Feb 2006 06:40 PM
Sounds like you don't need a JOIN at all, but a simple WHERE clause.

select client
from client
where group_cx = 'CInt005'
robertmazzo
New Member
New Member

--
23 Feb 2006 05:48 AM
Actually I realized this morning that you're 100% correct. However, I do have two queries I would like to put into one.
I also realized that my real problem is that there's an extra column called "extrn_name", which I first have to match againt another table. Then I need the first query I had mentioned above.

In other words first I need something like :

Select client from client where extrn_name = 'CUST001'

Now that I have the correct record from the Client table, I need to grab the records that match the "group_cx" column as follows :


select client from client where group_cx = @client


However, I would love to do this in one query.

Thanks,
Bob




robertmazzo
New Member
New Member

--
23 Feb 2006 06:15 AM
Thank you again for your input.

Here's what finally worked for me using a subquery :

select distinct(a.client) from client a
where a.group_cx in
(select b.client from client b where b.extrn_name = 'DEMO')
order by a.client

Have a great day !
Bob
mwesch
New Member
New Member

--
23 Feb 2006 05:13 PM
You will be better using an INNER JOIN than using a WHERE IN clause.

select distinct(a.client)
from client a
inner join client b
on a.group_cx = b.client
where b.extrn_name = 'DEMO'
order by a.client
You are not authorized to post a reply.

Acceptable Use Policy