That is my query.
I am trying to select the item details for the items the user has in their inventory, but I DO NOT want to select the ones that are in their collection.
So I'm trying to say where item_id IS NOT in the user_collection with their user id..
But this is returning an empty result when it should be returning around 5 rows.. it works fine when they have nothing in their collection, but as soon as I put one item in their collection, this query returns nothing.
SELECT i.item_img, i.item_id, i.item_name FROM item i JOIN user_item u ON u.item_id = i.item_id WHERE i.item_function='M' AND u.item_id NOT IN (SELECT u.item_id FROM user_collection WHERE u.user_id=6)