NOT IN help??

[Tenaciousmug]

This is my query:

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)

 

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.

 

Help?

[Barand]

try

SELECT i.item_img, i.item_id, i.item_name
            FROM item i
            INNER JOIN user_item u ON u.item_id = i.item_id
            LEFT JOIN user_collection c ON i.item_id = c.item_id AND u.user_id = 6
            WHERE i.item_function='M' AND c.item_id IS NULL