Need help with a complex MySQL select statement

[plogger]

I have a MySql table with approximately 2 million records named "ratings" with 3 columns, "user", "item" and "rating". I want to select all users that have rated two particular items but cannot figure out how to do it. I'm thinking it may involve a JOIN, but I'm not sure.

For example, if I wanted to select all "users" that have rated both "item" 344 and "item" 476 and place them into an array, how would I write that?

Thanks for any help in advance.

[Jstanfield]

start with people who rated item 344, then inner join people who rated item 476

-- this took me about 6 seconds to run on a 2m row table with no indexes

select distinct y1.user_id
from your_table y1
inner join your_table y2 on (y2.item=476 and y2.user_id=y1.user_id)
where y1.item = 344

-- create this index and you can knock 6 seconds down to a few milliseconds
-- execute show indexes from your_table to see if it might already exist

create index by_item_and_user_id on your_table(item,user_id);

[plogger]

Thanks, but there's only one table. I think your statement refers to two tables (y1 and y2). Can I simply replace y2 with y1?

Sorry if I'm missing something, but I'm still relatively new to this.

[plogger]

I got this from someone on another forum, and it works perfectly:

select user
from ratings
where item in (344,476)
group
by user
having count(distinct item) = 2

[Jstanfield]

I like that example more than mine. I learned something, too.

[brotherhood of LAN]

For reference plogger, you can actually join the same table onto itself, the "AS" allows you to have synonyms so the DB engine can understand which table instance you're referencing.

SELECT * FROM table AS t1 INNER JOIN table AS t2 ON t1.id = t2.id

[plogger]

That's good to know. Thanks for sharing it!