how to hack MySQL subselects?

[Anon]

Since MySQL doesn't do subselects, any ideas how I could pull this off in MySQL maybe with temp tables or something?

SELECT * FROM Inventory
WHERE (((Inventory.inventoryID) Not In (select inventoryID from inventory_assignments where date_in is null)));

[[deleted]]

Don't you love MySQL right now?

Load the second query into a temp table (TYPE=HEAP!)
then do a LEFT JOIN, a bit like

SELECT *
FROM inventory LEFT JOIN temp_table ON inventory.inventory_id=temp_table.inventoryid
WHERE temp_table.inventoryid IS NULL;

[Anon]

Try:

SELECT * FROM Inventory in, inventory_assignments ia
where in.inventoryID = ia.inventoryID
and ia.inventoryID is null;

[Anon]

I Did find that my SQL contruct did the job quit nicely.( See MY reply) what do you think?

[[deleted]]

This is what I thought of first too, but:

SELECT *
FROM Inventory
WHERE (((Inventory.inventoryID) Not In (select inventoryID from inventory_assignments where date_in is null)));

that means:

find all data from 'inventory' where the inventoryid is not equal to any inventoryid from "inventory_assignments" where date_in=NULL.

if 'inventory_assignments' has five inventoryIDs where date_in=NULL, the main query would do:

SELECT *
FROM inventory
WHERE inventoryID NOT IN (1,6,26,34,102);

There is no relation between inventoryid in inventory and inventoryid in invnetory_assignments.
That means it will report all inventoryids from inventory, even if they plain don't exist in inventory_assignments.

But your query says:

WHERE in.inventoryID = ia.inventoryID

this is only true if the inventoryID exists in both tables. So if there is an inventoryID that exists in 'inventory' but not in 'inventory_assignments', this clause would be false, and the entire inventoryid is skipped, instead of reported.

In my query I first use a temp table to get a list of all the inventoryID's that do have date_in IS NULL.
Those are the ones I want to rule out.

Then I do a LEFT JOIN on the inventory table and the temp table. The nice thing about a LEFT JOIN is that it will always add the joined columns, and if there is no match for the ON clause, the new columns will contain NULL.
So,

SELECT *
FROM inventory, temp ON inventory.inventoryid = temp.inventoryid

will return a list of all inventoryids from inventory, plus some extra info from the temp table if the temp table also had the same inventoryid in a row.
Now I wanted to find all the inventoryids that did not appear in the temp table, so I can just look for records where the extra columns are NULL.

Now I dp get all the inventoryids from inventory, and I only remove those ids that appear in inventory_assigments with a NULL in date_in.

Does anybody still understand me? :-)

[[deleted]]

After playing around a bit longer it turns out you can also do it without the temporary table:

SELECT *
FROM inventory LEFT JOIN inventory_assignments ON inventory.inventoryid=inventory_assignments.inventoryid
WHERE (inventory_assignments.inventoryid IS NULL) OR (inventory_assignments.inventoryid IS NOT NULL AND date_in IS NOT NULL)