select rows where not in multiple columns mysql

Question

I have a following result set:

request_id  |  p_id
66          |  10
66          |  10
66          |  10
66          |  22
66          |  22
76          |  23
76          |  24

I am trying to select rows that excludes records with certain combination values:

request_id   |   product_id
66           |   10
76           |   23

So the output result set should contain only these records:

66          |  22
66          |  22
76          |  24

I tried doing:

select * from `table` 
where request_id NOT IN (66, 76) AND product_id NOT IN (10, 22)

But this gives me empty resultset.

How do I exclude just the combination of those two values?

score 8 · Accepted Answer · answered Jun 24 '19 at 10:26

8

You can try below -

DEMO

select * from `table` 
where (request_id, p_id) NOT IN ((66, 10),(76,23))

OUTPUT:

request_id  p_id
66          22
66          22
76          24

answered Jun 24 '19 at 10:26

Fahmi

37,315
5
22
31

And what if the second table will contain `(67, 11)`? – Jun 24 '19 at 11:14
@SeaSharp then that wil be returned in query output. – TechnoCrat Jun 24 '19 at 13:05
@TechnoCrat. This query is not general. – Jun 24 '19 at 13:23

score 2 · Answer 2 · answered Jun 24 '19 at 10:42

2

Try use something like this:

SELECT DISTINCT *
FROM TABLE1
WHERE TABLE1.request_id NOT IN
(
    SELECT r_id 
    FROM TABLE2
)
AND TABLE1.p_id NOT IN
(
    SELECT p_id 
    FROM TABLE2
)

answered Jun 24 '19 at 10:42

score 0 · Answer 3 · answered Jun 24 '19 at 11:18

0

That is a better way:

SELECT DISTINCT *
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.request_id = TABLE2.r_id
                AND TABLE1.p_id = TABLE2.p_id
WHERE TABLE2.p_id IS NULL

answered Jun 24 '19 at 11:18

select rows where not in multiple columns mysql

3 Answers3