JOIN, and ignoring all entries on left if one on right matches criteria

[nogginj]

I have a table videos and users. i also have a table called videoViews which just links users to videos they have watched.

 

I want to choose a random entry from videos table, that the user has not viewed.

 

Currently, I do a join vid->videoViews, and get multiple entries, video entry repeated for each time a user viewed it.

 

I cannot do a 'WHERE vidViews.user != user', because there are multiple entries, and so it might still return me a particular video just joined to someone else's view.

 

So how can I exclude ALL instances of a video, if ONE instance of the video matches my criteria?

 

Am I using the wrong kind of join, or is there some funciton in MYSQL I am not familiar with?

[akitchin]

this sounds like an apt job for a subquery. you could try something like this:

 

SELECT * FROM videos WHERE video_id NOT IN (SELECT video_id FROM videoViews WHERE user_id='$user_id') ORDER BY RAND()

[nogginj]

NOT IN sounds like something I should read more about, thanks.

 

-j

[akitchin]

IN is actually a handy operator - you supply it with a comma-delimited list of items, and you can specify that a column's value be a part of that list, or not (as in this case).

 

MySQL manual entry

[nogginj]

Thank you for the handy pointer, my queries work perfectly now.

 

Now a bit of discussion, is this somehow an 'expensive' call to make? I mean is it going to severely slow down performance if the 'not in' list is huge?

[akitchin]

unfortunately i'm not that knowledgeable on performance issues, so i couldn't tell you off the top of my head. you could test by getting a JOIN query that works and running that against this sub-query method a few times. sub-query tutorials on the rest of the internetz might also include performance in their discussion.

 

if fenway sees this thread i'm sure he will be able to offer an expert answer .

[luca200]

I mean is it going to severely slow down performance if the 'not in' list is huge?

No, it will not.

Just be sure you have an index on user_id column in videoViews