CODEWITHSUNDEEP
mysql
Gaz Smith
Gaz Smith

Reputation: 1108

Using not exist in mysql to find IDs not yet assigned

I have a locnew table that has names of all locations in a warehouse so :

1 | acb1
2 | acb7
3 | aba2

i also have a location table which will assign products to the locations so it will have a locnew id (location_description_id) and then all the details of the products. I have wrote this query to show all the items in each location :

select l.*
     , ln.location_description 
  from location l
  join locnew ln 
    on ln.id = l.location_description_id
 where l.location_description_id != 376

which gives me everything we have in stock in them locations

enter image description here

Is there a way to use not exists to reverse this so i can find a list of all the "locnew" id's that dont have any products assigned to them in the location table?

Upvotes: 0

Views: 73

Answers (2)

Sami Altundag
Sami Altundag

Reputation: 238

You can use a left join

select l.*, ln.location_description 
    from location l
    left join locnew ln 
    on ln.id = l.location_description_id
    where ln.id is null

Joins are conceptualized as set operations in math.

When you join, you are taking the intersection of two sets.

When you left join, your are taking the intersection of two sets plus the remaining members of the left (first table) set.

When you right join, your are taking the intersection of two sets plus the remaining members of the right (second table) set.

However as you see, NONE of these three options give you what you need. What you need is the remaning items in the left set. This is why ln.id is null is added to the where clause. When there is not record on the right side, ln.is becomes null and the query gives you what you need.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

This query will give you desired result:

SELECT * FROM locnew ln
WHERE NOT EXISTS(SELECT 1 FROM location
                 WHERE location_description_id = l.id)

Upvotes: 0

Related Questions