CODEWITHSUNDEEP
mysqlsql
Duncan Ogle
Duncan Ogle

Reputation: 823

Exclude rows based on column value when second column value is found

I've tried searching for this and I'm not quite sure how to phrase exactly what I'm after, but I'll show what I've got and what I want. Also my first question here so I apologise if I get anything wrong!

Firstly, I have this link-table with foreign keys:

|ID|Value|
+--+-----+
|1 |   70|
|1 |  130|
|2 |   60|

Now, I'm trying to show me all ID's in this table where Value!=70. I've tried;

SELECT * FROM table WHERE Value != 70

This removes the 70 value, and now will show rows 1:130 and 2:60. But how would I go about excluding all values in the column where this has matched. E.g. if I say don't show me any ID's with the value 70 associated with them, I should only get the row with the ID of 2 out. I've tried;

SELECT DISTINCT * FROM table WHERE Value != 70

and

SELECT * FROM table WHERE Value != 70 GROUP BY ID

and 

SELECT DISTINCT * FROM table WHERE Value != 70 GROUP BY ID

but none of these work. Like I said I'm not sure exactly how to word what I'm after, but any help will be appreciated.

Upvotes: 2

Views: 2917

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Your second attempt is quite close to one method for solving this. The where clause simply needs to change to a having clause (along with a small change to the logic):

SELECT ID
FROM table t
GROUP BY ID
HAVING sum(Value = 70) = 0;

The expression in the having clause counts the number of rows where value is equal to 70. You want no such rows, hence the = 70. This form of the query is quite flexibly. Using the having clause you can check for more complex conditions, such as not having 70 or 71:

HAVING sum(Value = 70) = 0 AND
       sum(Value = 71) = 0;

Or not having 70 but having 71:

HAVING sum(Value = 70) = 0 AND
       sum(Value = 71) > 0;

And more complex combinations.

Upvotes: 0

eKek0
eKek0

Reputation: 23319

I don't know if I understand you well, but maybe this?

SELECT * FROM table WHERE ID NOT IN (
  SELECT ID FROM table WHERE Value = 70
)

I use 'NOT IN' and not != because it could be more than one ID whose associated value is 70.

Also you coud use:

SELECT * FROM table t1 WHERE NOT EXISTS(
  SELECT 1 FROM table t2 WHERE t2.ID = t1.ID AND t2.value = 70
)

Upvotes: 0

Fabio
Fabio

Reputation: 23510

You can use a subquery and NOT IN condition

SELECT tb.* FROM table tb
WHERE tb.ID NOT IN 
(
    SELECT a.ID FROM table a where a.Value = 70
)

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146607

Select * From table 
where id Not In 
        (Select distinct id 
         From table
         Where value = 70)

or 

Select * From table t
where Not exists
        (Select * From table
         where id = t.id
            and value = 70)

Upvotes: 4

Related Questions