CODEWITHSUNDEEP
sql
Ziil
Ziil

Reputation: 351

SQL - multiple rows, if one of the rows meets the condition, exclude all

How to exclude multiple rows if one of the rows meets the condition

example table:

  id/role_id/code
  1/1/112233
  1/2/221155
  1/3/332233
  5/1/323233
  5/3/988933
  6/1/389349
  6/2/112233
  6/3/232323

Now I want to find only these id-s, which has role_id=3 and exclude all rows, if one of them contains code=112233

In this example, results should show only this row: 5/3/988933

Upvotes: 1

Views: 1127

Answers (2)

Matt
Matt

Reputation: 14341

Gordon showed you how to do it via EXISTS and mentions IN but it can also be done via a left self join as follows:

SELECT t1.*
FROM
    Table t1
    LEFT JOIN Table t2
    ON t1.id = t2.id
    AND t2.code = 112233
WHERE
    t1.role_id = 3
    AND t2.id IS NULL

And while I don't like to use IN because it was mentioned here is how you could do it:

SELECT *
FROM
    Table
WHERE
    role_id = 3
    AND id NOT IN (SELECT ID
                FROM
                  Table
                WHERE
                  code = 112233
                  and ID IS NOT NULL)

Note I include the line ID IS NOT NULL because if you ever compare something to IN (NULL) you will not get your desired result. In this case an ID field is not likely to ever be null so you can probably remove that statement, I put it in to show the nuance.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If you want the original rows, then use exists or in:

select t.*
from t
where role_id = 3 and
      exists (select 1 from t t2 where t2.id = t.id and t2.code = 112233);

Upvotes: 0

Related Questions