Welcome to WebmasterWorld Guest from 54.167.229.178

Forum Moderators: open

Message Too Old, No Replies

Need help with a complex MySQL select statement

     
5:52 pm on Dec 5, 2011 (gmt 0)

New User

5+ Year Member

joined:Nov 14, 2010
posts: 8
votes: 0


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.
7:20 pm on Dec 5, 2011 (gmt 0)

New User

joined:Oct 26, 2011
posts: 13
votes: 0


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);
8:40 pm on Dec 5, 2011 (gmt 0)

New User

5+ Year Member

joined:Nov 14, 2010
posts: 8
votes: 0


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.
8:52 pm on Dec 5, 2011 (gmt 0)

New User

5+ Year Member

joined:Nov 14, 2010
posts:8
votes: 0


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
8:56 pm on Dec 5, 2011 (gmt 0)

New User

joined:Oct 26, 2011
posts: 13
votes: 0


I like that example more than mine. I learned something, too.
9:14 pm on Dec 5, 2011 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


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
9:29 pm on Dec 5, 2011 (gmt 0)

New User

5+ Year Member

joined:Nov 14, 2010
posts:8
votes: 0


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