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

SQL – Get all posts which are not voted by current user – Mysql

I have 2 MySQL tables, namely posts and votes
The posts table contains articles published by authors (users).
The votes table contains votes against the particular post by users.

I’d like to get all posts that have or haven’t been voted on by the current user.

What MySQL statement should I write to achieve the desired result?

Table 1 posts contains 4 columns.
post_id is the primary key, and author_id stores user_id from user table.

post_id
(int-11)		 title
(text)		 author_id
(int-11)		 created
(datetime)
1 This is 1st Post 2 2023-02-24 14:43:49
2 This is 2nd Post 5 2023-02-26 18:16:17
3 This is 3rd Post 1 2023-03-01 05:42:51
4 This is 4th Post 2 2023-03-01 08:20:15
5 This is 5th Post 8 2023-03-02 12:35:49

Table 2 votes contains 4 columns.
vote_id is the primary key, and post_id & author_id store values from the foreign tables, namely post and user. vote column can store enum(‘approve’, ‘reject’)

vote_id
(int-11)		 post_id
(int-11)		 user_id
(int-11)		 vote
enum(‘approve’, ‘reject’)
1 4 2 approve
2 2 3 reject
3 3 3 reject
4 1 2 approve

How to get all post rows that are not voted on by the current user or voted by the current user. (both condition seperately)

Also, how do I get the number of votes for a particular post in the same SQL statement?

Desired Result (Not Voted)
user_id = 3 is the current user.
user_id = 3 has already voted for post_id 2 & 3.
Except for post_id = 2 & 3, all rows should be returned.

post_id
(int-11)		 title
(text)		 author_id
(int-11)		 created
(datetime)
1 This is 1st Post 2 2023-02-24 14:43:49
4 This is 4th Post 2 2023-03-01 08:20:15
5 This is 5th Post 8 2023-03-02 12:35:49

I tried this SQL to fetch all rows for which the current user (3) has votes, but it is showing all rows.

SELECT p.*, v.*
FROM `posts` p
LEFT JOIN `votes` v ON p.post_id = v.post_id 
LEFT JOIN `votes` v2 on p.post_id = v2.post_id and v2.user_id='3';
Tags:

2

Answers


  1. 0

    For example:

    SELECT p.*
FROM `posts` p
LEFT JOIN `votes` v ON p.post_id = v.post_id 
WHERE v.user_id = 3

    and WHERE v.user_id <> 3
    for teh other case.
    If I understood correctly.

    Login or Signup to reply.
  2. 0

    The main problem of you have tried: The current user filter is part of left outer join.

    To get the result you want, you can use NOT IN operator and post_id from votes to buid your query:

    SELECT *
FROM posts 
WHERE post_id NOT IN (
     SELECT post_id
     FROM votes
     where user_id = '3' 
)
    post_id title author_id created
    1 This is 1st Post 2 2023-02-24T14:43:49Z
    4 This is 4th Post 2 2023-03-01T08:20:15Z
    5 This is 5th Post 8 2023-03-02T12:35:49Z
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search