Ragith Thomas Ragith Thomas - 23 days ago 6
SQL Question

SQL query to get table rows whose columns should not match with other table columns when passing same column id

Thanks in advance, I have two tables carts and checks.
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 and i am passing userid = $id from my php function. Here the userid is same for both the table
Carts table:

id |userid | username | orderid | exam_name |
1 |13 | Gautham |437b310v671N888M6720 | PMP |
2 |13 | Gautham |437b310v671N888M6720 | CAPM |
3 |13 | Gautham |437b310v671N888M6720 | Prince2 |
4 |14 | Rakesh |678g310v671N888M6720 | Prince2 |

checks table:
id |userid | username | orderid | exam_name |
1 |13 | Gautham |437b310v671N888M6720 | PMP |
2 |13 | Gautham |437b310v671N888M6720 | CAPM |

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 and i am passing userid = $id from my php function. Here the userid is same for both the table

1 |13 | Gautham |437b310v671N888M6720 | Prince2 |
2 |14 | Rakesh |678g310v671N888M6720 | Prince2 |

I got the below sql query from reference but its not working

select c.* from carts c where (c.orderid, c.exam_name) not in (select ch.order_id, ch.exam from checks ch where ch.userid = 13)

Answer
 select c.* from carts c where NOT EXISTS (SELECT 1 FROM checks WHERE checks.orderid = c.orderid AND c.exam_name = checks.exam_name)
    AND C.userid = 13
Comments