CODEWITHSUNDEEP
sqlmysql
Steve Gattuso
Steve Gattuso

Reputation: 7832

Help with creating a rather complex sql statement

Hey everyone, I wasn't really sure how to describe this in the form of a google search, so I've decided to come here and see if you guys can help. Basically, I want to know how I can translate the following pseudo-sql into an actual mysql query:

Select one random row 
  from `posts` 
 where (the user's id, which is supplied by the script) is not found in the `votes` table 
        with a post id of (the selected post's id).

Thank you so much if you can help.

Upvotes: 2

Views: 103

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332661

NOT IN

  SELECT p.*,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM POSTS p
    JOIN (SELECT MAX(t.id) - 1 'm_id'
            FROM POSTS t) x
   WHERE p.id NOT IN (SELECT v.postid 
                        FROM VOTES v 
                       WHERE v.userid = @UserID)
ORDER BY rand_ind
   LIMIT 1

LEFT JOIN/IS NULL

   SELECT p.*,
          FLOOR(1 + RAND() * x.m_id) 'rand_ind'
     FROM POSTS p
     JOIN (SELECT MAX(t.id) - 1 'm_id'
         FROM POSTS t) x
LEFT JOIN VOTES v ON v.postid = p.id
             AND v.userid = @UserID
    WHERE v.postid IS NULL
 ORDER BY rand_ind
    LIMIT 1

NOT EXISTS

  SELECT p.*,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM POSTS p
    JOIN (SELECT MAX(t.id) - 1 'm_id'
            FROM POSTS t) x
   WHERE NOT EXISTS(SELECT NULL 
                      FROM VOTES v 
                     WHERE v.userid = @UserID
                       AND v.post_id = p.id)
ORDER BY rand_ind
   LIMIT 1

Summary

  1. LEFT JOIN/IS NULL and NOT IN are equally efficient; NOT EXISTS is less efficient.
  2. ORDER BY RAND() is not recommended because it does not scale as the amount of data increases (graph). Related question.

Upvotes: 1

Dan Soap
Dan Soap

Reputation: 10248

SELECT p.* 
FROM `posts`
LEFT JOIN `votes` ON (posts.post_id=votes.post_id AND votes.user_id=@UserID)
WHERE votes.user_id is null
ORDER BY rand()
LIMIT 1;

From my experience, joins are significantly faster than subselects, especially in combination with the NOT IN clause

Upvotes: 1

Lucero
Lucero

Reputation: 60236

Something like this:

SELECT *
FROM Posts
WHERE Posts.ID NOT IN (
    SELECT Votes.PostID FROM Votes WHERE Votes.UserID=@UserID
)
ORDER BY Rand()
LIMIT 1

Upvotes: 6

Related Questions