Click here to Skip to main content
14,449,862 members
Rate this:
Please Sign up or sign in to vote.
See more:
My data looks something like this:
Key        ColA          ColB
0001          007           X012           **

0002          007           X012
0002          008           X012
0002          011           X012

0003          007           X012           **

0004          007           X012
0004          008           X012


What I am trying to do is return only those keys that have column A = '007' and Column 'B' = 'X012' BUT no other rows for the same key can have an 'X012' value.
Looking at the data above I want only the rows marked with ** returned as they contain 007 and X012 but no other rows that contain X012.

What I have tried:

I have tried using NOT IN , I have tried EXCEPT , using EXISTS with NOT EXISTS, SELECT within SELECT but I either get all data returned that includes X012 or I get no data returned.
Posted
Updated 7-Feb-20 3:41am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Use GROUP BY to limit the Key values, and then JOIN that with your original table:
SELECT a.[Key], a.ColA, a.ColB 
FROM MyTable a
JOIN (SELECT [Key] FROM MyTable
      GROUP BY [Key] 
      HAVING COUNT([Key]) = 1) b
  ON a.[Key] = b.[Key]
WHERE a.ColA = '007'
  AND a.ColB = 'X012'
   
v3
Comments
Maciej Los 7-Feb-20 8:43am
   
Interesting... You are getting data if group count is equal to 1, but i'm getting data where key column has odd value...

:D
OriginalGriff 7-Feb-20 8:45am
   
I suspect he may want to add "AND ColB = 'X012'" to the HAVING clause, but from his example it's difficult to tell.
OriginalGriff 7-Feb-20 8:47am
   
Or similar - that wouldn't compile as ColB is not in the GROUP BY clause - it'd be a new WHERE clause, obviously :doh:
Rate this:
Please Sign up or sign in to vote.

Solution 2

If you want to get data for odd values in key column:
SELECT *
FROM TableName
WHERE ColumnA = '007' AND ColumnB = 'X012' AND CONVERT(ColumnKey, INT) % 2 != 0
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100