MySQL NOT IN Select

Question

I have a following table in my project

+----+--------+-----------+-----------+
| id | old_id | op_status | tr_status |
+----+--------+-----------+-----------+
|  1 |        | issue     | Approved  |
|  2 |        | issue     | Approved  |
|  3 |        | issue     | Approved  |
|  4 |      1 | issue     | Issued    |
|  5 |      3 | issue     | Issued    |
+----+--------+-----------+-----------+

I want to select records WHERE tr_status='Approved' and NOT IN id in the old_row_id. In this example no need to select id(s) 1 and 3 that are in old_row_id as the following result.

+----+--------+-----------+-----------+
| id | old_id | op_status | tr_status |
+----+--------+-----------+-----------+
|  2 |        | issue     | Approved  |
+----+--------+-----------+-----------+

I used the following query.

SELECT id, old_row_id, op_status, tr_status FROM table WHERE id NOT IN (old_row_id).

But outs the following result.

+----+--------+-----------+-----------+
| id | old_id | op_status | tr_status |
+----+--------+-----------+-----------+
|  1 |        | issue     | Approved  |
|  2 |        | issue     | Approved  |
|  3 |        | issue     | Approved  |
+----+--------+-----------+-----------+

What may be wrong with me ? can anyone help me ?

See about joins

Strawberry
– Strawberry

2020-07-09 17:06:14 +00:00
Commented Jul 9, 2020 at 17:06 — Strawberry
– Strawberry, Commented Jul 9, 2020 at 17:06

Tim Biegeleisen · Accepted Answer · 2020-07-09 17:10:14Z

2

I would phrase your query using exists logic:

SELECT t1.id, t1.old_id, t1.op_status, t1.tr_status
FROM yourTable t1
WHERE
    t1.tr_status = 'Approved' AND
    NOT EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.old_id = t1.id);

Demo

answered Jul 9, 2020 at 17:10

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

Wai Ha Lee · Accepted Answer · 2020-07-09 20:09:05Z

1

Try doing something like this:

SELECT id, old_id, op_status, tr_status 
FROM table 
WHERE id NOT IN (SELECT DISTINCT old_id FROM table)
AND tr_status = 'Approved'

edited Jul 9, 2020 at 20:09

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Jul 9, 2020 at 17:08

rboling

7271 gold badge4 silver badges8 bronze badges

Comments

Gabe Gates · Accepted Answer · 2020-07-09 17:10:44Z

1

You will want to left join the table to itself on the old ID, then eliminate the records where there is a match.

For example:

SELECT A.id, A.old_row_id, A.op_status, A.tr_status 
FROM table A
LEFT JOIN table B ON A.id = B.old_row_id
WHERE B.id IS NULL
AND A.tr_status = 'Approved';

answered Jul 9, 2020 at 17:10

Gabe Gates

1,0501 gold badge16 silver badges23 bronze badges

Comments

Charlie · Accepted Answer · 2020-07-09 17:15:16Z

-1

In they way you've tried to solve it, you're matching the old_id with the value of the row itself. You must derivate the table to create a cartesian product:

SELECT id, old_id, op_status, tr_status 
FROM table 
WHERE id NOT IN (SELECT IFNULL(old_id, 0) FROM table)
AND tr_status = 'Approved'

Also IFNULL to also include those record in the subquery, otherwise can't be compared with null

answered Jul 9, 2020 at 17:15

Charlie

1

1 Comment

Charlie Over a year ago

No idea who downvotes this answer, but the accepted answer so far does not work if old_id contains NULL since NULL does not matches with NULL

Collectives™ on Stack Overflow

MySQL NOT IN Select

4 Answers 4

Demo

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related