Filter records in database [closed]

enter image description hereI have a table with 3 columns Id,Unit and Process:

|Id  |Unit  |Process   |
|----|------|----------|
|100 |1     |0         |
|100 |0     |5         |
|101 |1     |0         |

Output

|Id  |Unit  |Process   |
|----|------|----------|
|101 |1     |0         |

---

I want to exclude all ID’s which have process value as 5.

So ID 100 has a process 5, so all rows for ID 100 will be removed Also I want addtional conditions all records should have unit value as 1 in output set.

Here what I tried but I need short query

Select id,unit, process from table a where id in
(
Select distinct id from table a where unit=1
)
And id not in
(
Select distinct id from table a where process=5
)

Answer

You’re looking for WHERE NOT EXISTS.

Data set up:

CREATE TABLE mytable(
   Id      INTEGER  NOT NULL
  ,Unit    INTEGER  NOT NULL
  ,Process INTEGER  NOT NULL
);
INSERT INTO mytable(Id,Unit,Process) VALUES (100,1,0);
INSERT INTO mytable(Id,Unit,Process) VALUES (100,0,5);
INSERT INTO mytable(Id,Unit,Process) VALUES (101,1,0);

Query:

SELECT 
  *
FROM mytable AS m1
WHERE 
  m1.Unit = 1
  AND
  NOT EXISTS (SELECT 1 
              FROM mytable AS m2 
              WHERE m1.ID = m2.ID AND m2.Process = 5);

Results:

+-----+------+---------+
| Id  | Unit | Process |
+-----+------+---------+
| 101 |   1  |       0 |
+-----+------+---------+