CODEWITHSUNDEEP
mysql
Checksum
Checksum

Reputation: 3240

A simple MySQL query help

Been stuck with this fairly simple MySQL query for a day now! Can't believe how quickly I've forgotten the basics. I have 3 tables - user, post and favourite_post. The post table has a user_id that is a FK to user table.
The favourite_post table has 

user_id REFERENCES user(id) 
post_id REFERENCES post(id)
timestamp

When a user favourites a post, his user_id, the post_id and timestamp are inserted into the favourite_post table.

I use the following query to retrieve the 15 more recent posts

SELECT post.id, post.text, post.timestamp, post.user_id, user.username 
FROM post 
   INNER JOIN user 
   ON post.user_id = user.id 
ORDER BY post.id 
DESC LIMIT 15;

What I need to do along with that is check if each post has been favourited by the current user(say user_id = 1) by joining with the favourite_post table.

Upvotes: 2

Views: 92

Answers (2)

Shef
Shef

Reputation: 45599

    SELECT p.id, p.text, p.timestamp, p.user_id, u.username, 
           IF(ISNULL(fp.post_id), 'No', 'Yes') has_favourite
      FROM post p
INNER JOIN user u
        ON p.user_id = u.id 
 LEFT JOIN favourite_post fp
        ON p.id = fp.post_id
       AND u.id = fp.user_id
  ORDER BY p.id DESC 
     LIMIT 15;

Upvotes: 2

Marco
Marco

Reputation: 57593

Try this:

SELECT p.id, p.text, fp.timestamp, p.user_id, u.username 
FROM post p INNER JOIN user u
    ON p.user_id = u.id
LEFT JOIN favourite_post fp
    ON fp.user_id = p.user_id AND fp.post_id = p.id
WHERE p.user_id = your_user_id
ORDER BY p.id DESC 
LIMIT 15;

Upvotes: 0

Related Questions