On 16-May-07, at 4:05 PM, PFC wrote:
This makes queries hard to optimize. Consider the table (user_id, item_id) meaning user selected this item as favourite.
If you want to know which users did select both items 1 and 2, you have to do a self-join, something like :
SELECT... FROM favourites a, favourites b WHERE a.user_id = b.user_id AND a.item_id=1 AND b.item_id = 2
You could get users who have selected both items 1 and 2 without doing a self-join with a query like the following:
select user_id from favourite where item_id = 1 or item_id = 2 group by user_id having count(*) = 2;