CODEWITHSUNDEEP
mysqlsql
Roman
Roman

Reputation: 61

How to exclude certain entries from a query [SQL]

I do have the following issue: Let's say you have a table that looks like this:

"ExampleTable"

NotPrimID     Number
0             13
0             13
0             14
1             14
1             14
2             13
2             13

Question: I want to have an query which will deliver all the NotPrimID's which do have the Number as 13, however if the Number of a NotPrimID is also 14 etc. it should be automatically excluded from the list.

Upvotes: 0

Views: 86

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

You could use exists logic here:

SELECT DISTINCT NotPrimID
FROM ExampleTable t1
WHERE Number = 13 AND NOT EXISTS (SELECT 1 FROM ExampleTable t2
                                  WHERE t2.NotPrimID = t1.NotPrimID AND t2.Number = 14);

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

If you just want the ids, then use group by and having:

select notprimid
from t
where number in (13, 14)
group by notprimid
having max(number) = 13;  -- has 13 but not 14

If you want the original rows, one method is exists/not exists:

select t.*
from t
where exists (select 1 
              from t t2
              where t2.notprimid = t.notprimids and
                     t2.number = 13
             ) and
      not exists (select 1 
                  from t t2
                  where t2.notprimid = t.notprimids and
                        t2.number = 14
                 );

Upvotes: 1

Related Questions