Sohil Desai Sohil Desai - 2 months ago 5
MySQL Question

Subtract rows of one table from another table

I have two tables in MySql which contain records something like this.

table-A table-B
--------------------- ---------------------
|prod_id | cat_id | |prod_id | cat_id |
--------------------- ---------------------
|1 |1 | |1 |1 |
|2 |1 | |2 |1 |
|3 |1 | |3 |1 |
|4 |1 |
|5 |1 |


Now I want to compare this two tables and my expected result is.

---------------------
|prod_id | cat_id |
---------------------
|4 |1 |
|5 |1 |


I have checked about 'EXPECT', 'MINUS' in sql but they do not support in MySql.

Can anybody help me? Thanks is advance.

Answer

Data in TableA

SELECT * FROM TableA;
+---------+--------+
| prod_id | cat_id |
+---------+--------+
|       1 |      1 |
|       2 |      1 |
|       3 |      1 |
+---------+--------+
3 rows in set (0.00 sec)

Data in TableB

SELECT * FROM TableB;
+---------+--------+
| prod_id | cat_id |
+---------+--------+
|       1 |      1 |
|       2 |      1 |
|       3 |      1 |
|       4 |      1 |
|       5 |      1 |
+---------+--------+
5 rows in set (0.00 sec)

Resultant query

SELECT b.* 
FROM TableB b 
LEFT JOIN TableA a 
ON a.prod_id = b.prod_id AND a.cat_id = b.cat_id 
WHERE a.prod_id IS NULL;
+---------+--------+
| prod_id | cat_id |
+---------+--------+
|       4 |      1 |
|       5 |      1 |
+---------+--------+
2 rows in set (0.00 sec)

sqlfiddle for same.

Comments