homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Need help with a complex MySQL select statement

Msg#: 4394475 posted 5:52 pm on Dec 5, 2011 (gmt 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.



Msg#: 4394475 posted 7:20 pm on Dec 5, 2011 (gmt 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);


Msg#: 4394475 posted 8:40 pm on Dec 5, 2011 (gmt 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.


Msg#: 4394475 posted 8:52 pm on Dec 5, 2011 (gmt 0)

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

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


Msg#: 4394475 posted 8:56 pm on Dec 5, 2011 (gmt 0)

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

brotherhood of LAN

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

Msg#: 4394475 posted 9:14 pm on Dec 5, 2011 (gmt 0)

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


Msg#: 4394475 posted 9:29 pm on Dec 5, 2011 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved