Ragith Thomas Ragith Thomas - 1 month ago 9
SQL Question

SQL query to get table rows whose columns should not match with other table columns

Thanks in advance,

Actually I have two tables carts and checks. Carts table contains rows as below

id |username |orderid | exam_name | price
1 | Rajesh | ABC123 | PMP | $60
2 | Rajesh | ABC123 | CPM | $70
3 | David | ABC789 | ITIL | $80


checks
table contains rows as below

id |username |order_id | exam | price
1 Rajesh | ABC123 | PMP | $60
2 Rajesh | ABC123 | CPM | $70


I need a row data of carts table whose orderid column and exam_name column should not match with checks table order_id column and exam column

Something like this as below:

id |username |orderid | exam_name | price
1 | David | ABC789 | ITIL | $80

Answer

One method is not exists:

select c.*
from carts c
where not exists (select 1
                  from checks ch
                  where ch.orderid = c.orderid and ch.exam_name = c.exam_name
                 );

A similar method is left join:

select c.*
from carts c left join
     checks ch
     on ch.orderid = c.orderid and ch.exam_name = c.exam_name
where ch.orderid is null;

And in some databases you can use not in:

select c.*
from carts c
where (c.orderid, c.exam_name) not in (select ch.orderid, ch.exam_name from checks ch);