How to find a row does not contain value in another column

Question

I have a table like this :

Id | Value1 | Value2 1 | x | 2 2 | x | 3 3 | x | 7 4 | y | 3 5 | z | 1 6 | z | 7 7 | c | 5 8 | c | 6

How I can get the 'Value1' that doesn't contain value 7 in column 'Value2'

So, the result should be :
y
c

Thanks.

select val1 from yourtable where val2<>7? Or is that too easy? — Marc B
– Marc B, Commented Oct 17, 2016 at 16:00
@Drew I believe this may have been wrongfully marked as a duplicate. Look at the result-set. If this was a simple "Where not = 7", then it would return X and Z, not just Y and C. OP was not super clear in this, however PaulF pointed out the same error in my own answer below. — Tyler Roper
– Tyler Roper, Commented Oct 17, 2016 at 16:24
@Santi - thanks I was just about to make that comment & have marked the question for re-opening — PaulF
– PaulF, Commented Oct 17, 2016 at 16:25
@MarcB: too easy - OP wants to eliminate ALL rows where value1 is x or z, because some of those rows contain value2 = 7. So not simply selecting only rows not equal to 7 — PaulF
– PaulF, Commented Oct 17, 2016 at 16:27
@Santi this is a classic case of a question that does not benefit this site. Ergo we move it toward deletion — Drew
– Drew, Commented Oct 17, 2016 at 16:37

Tyler Roper · Accepted Answer · 2016-10-17 16:52:34Z

1

EDITED

As pointed out by PaulF in the comments below, I may have misunderstood your question. Try something like this instead...

SELECT DISTINCT Value1 FROM MyTable a WHERE NOT EXISTS (SELECT Value1 FROM MyTable b WHERE b.Value1 = a.Value1 AND b.Value2 = 7)

answered Oct 17, 2016 at 16:00

21.7k6 gold badges35 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Wont your query return x,y, z & c - OP wants to eliminate x & z because they have values containing 7

@PaulF Oh man, you may be correct! I misunderstood the question I suppose. I'll edit.

@PaulF Perhaps this is better!

Beat me to it again - I had DISTINCT in the subquery too, not needed in the sample data - but maybe would improve efficiency in a large table

as you don't know the schema, this could be a dangerous use of NOT IN which should be avoided as such

|