By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,128 Members | 639 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,128 IT Pros & Developers. It's quick & easy.

how to throw out a 'group' of values based on different rows

P: 8
If I have the following table:

Client# ....... Value

24 ................ A
24 ................ G
24 ................ F
25 ................ A
25 ................ B
25 ................ C

I want to retrieve all Client#s where Value equals both A AND B. I'm not sure how to specify this, since 'A' and 'B' are on different rows and only linked by the Client#. I've tried using the group by / having clauses, but that seemed to be the wrong path.

Any help would be greatly appreciated. I've spent a couple weeks (not consistently...a half hour here, a half hour there) trying to figure this out and it's beginning to hurt my head. :P
Dec 13 '07 #1
Share this Question
Share on Google+
2 Replies

Jim Doherty
Expert 100+
P: 897
If I have the following table:

Client# ....... Value

24 ................ A
24 ................ G
24 ................ F
25 ................ A
25 ................ B
25 ................ C

I want to retrieve all Client#s where Value equals both A AND B. I'm not sure how to specify this, since 'A' and 'B' are on different rows and only linked by the Client#. I've tried using the group by / having clauses, but that seemed to be the wrong path.

Any help would be greatly appreciated. I've spent a couple weeks (not consistently...a half hour here, a half hour there) trying to figure this out and it's beginning to hurt my head. :P
This will give you 'Unique' clients where each client has BOTH an A and a B listed somewhere in the stack. In other words you will only get the client mentioned once if that A&B pair is in evidence

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DISTINCT TOP 100 PERCENT a.Client
  3. FROM         dbo.Clients a INNER JOIN
  4.                          (SELECT     Client, Value
  5.                             FROM         Clients
  6.                             WHERE     [value] = 'b') b ON a.Client = b.Client AND a.[Value] <> b.[Value]
  7. WHERE     (a.[Value] = 'a') OR
  8.                      (a.[Value] = 'b')
  9.  
Jim :)
Dec 14 '07 #2

amitpatel66
Expert 100+
P: 2,367
If I have the following table:

Client# ....... Value

24 ................ A
24 ................ G
24 ................ F
25 ................ A
25 ................ B
25 ................ C

I want to retrieve all Client#s where Value equals both A AND B. I'm not sure how to specify this, since 'A' and 'B' are on different rows and only linked by the Client#. I've tried using the group by / having clauses, but that seemed to be the wrong path.

Any help would be greatly appreciated. I've spent a couple weeks (not consistently...a half hour here, a half hour there) trying to figure this out and it's beginning to hurt my head. :P
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT g.client#,g.value FROM
  3. (SELECT client# FROM table1 WHERE value IN ('A','B') GROUP BY client# HAVING COUNT(value) = 2) y, table1 g
  4. WHERE y.client# = g.client#
  5.  
  6.  
Dec 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.