homepage Welcome to WebmasterWorld Guest from 54.204.77.26
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Need help with a complex MySQL select statement
plogger




msg:4394477
 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.

 

Jstanfield




msg:4394501
 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);

plogger




msg:4394526
 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.

plogger




msg:4394529
 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)
group
by user
having count(distinct item) = 2

Jstanfield




msg:4394530
 8:56 pm on Dec 5, 2011 (gmt 0)

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

brotherhood of LAN




msg:4394536
 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

plogger




msg:4394541
 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