CODEWITHSUNDEEP
phpmysqlsql
Ayan Biswas
Ayan Biswas

Reputation: 65

create a result set difference between two tables in Mysql

I have two table suppose products and auto_assign_prod_list. I want to populate a dropdown list with the id of products table that are not present in auto_assign_prod_list table.

Suppose, product table contain

Id
------
1
2
3
4
5

auto_assign_prod_list table contain

Id
-----
1
5

So, my result set will be

2
3
4

How is it possible using MySQL and PHP ?

Upvotes: 0

Views: 109

Answers (2)

Raging Bull
Raging Bull

Reputation: 18737

Try this:

SELECT Id FROM product
WHERE Id NOT IN (SELECT Id FROM auto_assign_prod_list)

It will select the ids from product table which are not in auto_assign_prod_list table.

Result:

Id
------
2
3
4

See result in SQL Fiddle.

Upvotes: 1

juergen d
juergen d

Reputation: 204746

use a left join

select p.id
from products p
left join auto_assign_prod_list a on a.id = p.id
where a.id is null

SQLFiddle demo

See this great explanation of joins

Upvotes: 0

Related Questions