CODEWITHSUNDEEP
sql
Ragith Thomas
Ragith Thomas

Reputation: 105

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

Upvotes: 0

Views: 69

Answers (4)

Mansoor
Mansoor

Reputation: 4192

SELECT * FROM Carts 
WHERE NOT EXISTS (SELECT 1 FROM checks 
    WHERE checks.OrderId = Carts.OrderId 
    and Carts.exam_name = checks.exam_name)

Upvotes: 0

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

SELECT *
from Carts 
where  exam_name  not in (select  exam       from checks) 
and id  not in (select id  from  checks)

Upvotes: 0

Viki888
Viki888

Reputation: 2774

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

Hope this should solve your problem.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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);

Upvotes: 2

Related Questions