MySQL - a subquery and a WHERE IN

Question

I have a table of items properties, and a table of user_favorites structured like this:

• id
• item_id
• user_id
• favorite (bool)

When a user "favorites" an item it adds a row to this table using the item id, the user id, and sets the favorite property to 1 (if they "unfavorite" it's changed to 0).

I'm trying to figure out how to show the user's favorites when they view lists of products.

I currently do a query based on user search input and get an array of product IDs. Now I want to do a query amongst those products to create a table of product information - and I want it to include whether or not the item is a user favorite.

I've tried something like this:

select i.tid, i.name as name, i.image as image,  (SELECT favorite FROM user_favorites WHERE user_id=77 ) as favorite
    FROM items i 

    left join user_favorites ufav on (ufav.item_id = i.tid)
    WHERE i.tid IN (79, 98, 105 . . .) 
    order by favorite DESC, name ASC

..but of course I get a Subquery returns more than 1 row error

I've also tried something like this:

   select i.tid, i.name as name, i.image as image,  ufav.favorite as favorite
    FROM items i 
    left join user_favorites ufav on (ufav.item_id = i.tid)
    WHERE i.tid IN (79, 98, 105 . . .) AND ufav.user_id=77
    order by favorite DESC, name ASC

. . . but this only returns items that are favorited (as you'd expect). I'd like to return all the items in the list of IDs, but I also want to know which items have not been "favorited".

Can I do this in one query, or is my best option to run two separate queries?

Answer 1

Try this ::

select 
i.tid, 
i.name as name, 
i.image as image,  
ifnull(favorite,false) as isFavorite

FROM items i 
left join user_favorites ufav on (ufav.item_id = i.tid)
WHERE i.tid IN (79, 98, 105 . . .)  and ufav.user_id=77
    order by favorite DESC, name ASC

Answer 2

I think you need to use CROSS JOIN here because you want to show all items to all users, example

SELECT  d.item_ID, d.Itemname, d.user_ID, 
        COALESCE(c.`favorite`, 0) Favorite
FROM
        (
          SELECT   a.item_ID, a.Itemname, b.user_ID
          FROM     items a CROSS JOIN 
                   (
                       SELECT DISTINCT user_ID
                       FROM user_favorites
                   ) b
        ) d LEFT JOIN user_favorites c
            ON c.item_ID = d.item_ID AND
              c.user_ID = d.user_ID
-- WHERE d.user_ID = 1        -- this is you condition
ORDER BY d.user_ID, d.Item_ID

• SQLFiddle Demo