CODEWITHSUNDEEP
phpmysql
visevo
visevo

Reputation: 861

MySQL NOT IN with condition

I need to write a query that will pull all pieces of hardware that are unassigned to a user. My tables that look like this:

table: hardware ID, brand, date_of_purchase, purchase_price, serial_number, invoice_location

table: assigned_equipment ID, user_id, object_id, object_type, is_assigned, date_assigned

Once a piece of hardware is checked out to a user, a new entry in assigned_equipment is made, and the column is_assigned is set to 1. It can be 0 if it is later unassigned.

That being said, my query looks like this: SELECT * FROM hardware WHERE ID NOT IN (SELECT object_id FROM assigned_equipment);

I need a conditional statement that would add WHERE is_assigned = 0 otherwise if there's an entry it will not list. Ideas?

Upvotes: 0

Views: 51

Answers (3)

diggersworld
diggersworld

Reputation: 13080

If you take a semantic approach then the is_assigned column should not be required - as only assigned items should appear in the assigned_equipment table.

Which would make your query:

SELECT *
FROM `hardware`
WHERE `id` NOT IN (
    SELECT `object_id`
    FROM `assigned_equipment`
);

This of course means that when an item becomes unassigned you DELETE the row from the assigned_equipment table.

In my opinion this is better as it means you're not storing unnecessary data.

Upvotes: 0

dognose
dognose

Reputation: 20889

Simple extend the subquery to contain only assigned items:

SELECT * FROM hardware 
WHERE ID NOT IN 
  (SELECT object_id FROM assigned_equipment WHERE is_assigned = 1);

So, every matching id is NOT in the subselect - therefore unassigned.

Columns in the assignment table with is_assigned=0 are no longer part of the subresult, and therefore part of your outer result.

Upvotes: 2

JRL
JRL

Reputation: 850

You can't do this without a JOIN so you should ditch the subselect.

SELECT 
    hardware.* 
FROM 
    hardware h 
LEFT JOIN 
    assigned_equipment e 
ON (e.object_id = h.id) 
WHERE 
    e.id IS NULL 
    OR 
    (e.is_assigned = 0 AND e.user_id = ?);

Upvotes: 0

Related Questions