Question 198369 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I construct a MySQL query to select posts with specific associated information?

I am working with three tables in my MySQL database, and I need some help constructing a SELECT query.

The structure of my tables is as follows:

  1. posts table: (postId, userId, post)
  2. likes table: (userId, postId)
  3. users table: (userId, email, password)

In the likes table, if a user with userId = 1 liked a post with postId = 'abc', there will be a record (1, 'abc').

Now, I want to create a query that selects posts and returns the following columns:

  1. posts.postId
  2. posts.userId
  3. posts.post
  4. users.email (This should be the email of the author of the post)
  5. likes_count (This should be the count of likes for the post)
  6. isLiked (This should be true if a specified user has liked the
    post, and false otherwise. I will pass the userId for this
    condition)

Could anyone provide me with the SQL query that would accomplish this? I would really appreciate the help.

Tags:

2

Answers


  1. 0

    You can modify the following SQL query to retrieve the desired information:

    SELECT 
    p.postId,
    p.userId,
    p.post,
    u.email AS author_email,
    likes.likes_count,
    CASE WHEN likes.userId IS NOT NULL THEN TRUE ELSE FALSE END AS isLiked
FROM 
    posts p
    INNER JOIN users u ON p.userId = u.userId
    LEFT JOIN (
        SELECT 
            postId,
            COUNT(*) AS likes_count,
            MAX(CASE WHEN userId = <user_id> THEN 1 ELSE 0 END) AS userId
        FROM 
            likes
        GROUP BY 
            postId
    ) likes ON p.postId = likes.postId

    Make sure to replace <user_id> with the actual user ID for which you want to check the liked status.

    Login or Signup to reply.
  2. 0 
    SELECT 
    Posts.postId,
    Posts.userId,
    Posts.post,
    Author.email AS author_email,
    COUNT(likes.userid) as likes_count ,
    MAX(CASE WHEN likes.userid = <userid> THEN 'TRUE' ELSE 'FALSE' END) as IsLiked
FROM Posts

inner join Users as Author ON Author.userid=Posts.userid
LEFT join likes On likes.Postid=Posts.postId

group by Posts.postId,
        Posts.userId,
        Posts.post,
        Author.email
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search