NOT IN help??

[Tenaciousmug]

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.

Help? :)

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)

[blocblue]

I don't see that your query is correct. The subquery you have used in the NOT IN statement uses the u alias, which isn't defined within the subquery itself. Likewise, the user collection table doesn't join the user item table, which is defined as using the u alias.

Have you tried changing your query to use a join?

SELECT i.item_id, i.item_img, i.item_name
FROM item i
INNER JOIN user_item u ON (u.item_id = i.item_id)
LEFT JOIN user_collection uc ON (i.item_id = uc.item_id)
WHERE u.user_id = 6
AND uc.item_id IS NULL

This should (untested) only find items that are not in the user collection, because the join has returned null.

hello,

    WHERE i.item_function='M' AND u.item_id NOT IN (SELECT u.item_id FROM user_collection WHERE u.user_id=6)

should be changed into

    WHERE i.item_function='M' AND u.item_id NOT IN (SELECT u.item_id FROM user_collection **u** WHERE u.user_id=6)

or simply drop alias u:

      WHERE i.item_function='M' AND u.item_id NOT IN (SELECT item_id FROM user_collection WHERE user_id=6)

-- 1stDAN

Question to everyboy in here: can someone tell me how to quote (cite) the code lines 1 to 4 Tena... wrote?

Also: I selecetd "bold" to bolding the u and u was added. the ** should not be shown, for it could confuse one.