0

I have 4 tables.

post: id, title

tag: id, name

post_tag: id, post_id, tag_id

user_read_activity: id, user_id, post_id

User_read_activity contains posts that user has read. Post_tag contains post that are linked to tags. one post can have more than one tag. I need to fetch user unread posts based on certain tags that the user follows and also remove certain posts which the user may not like.

I have come up with this code. But this is wrong. This also gives me user read posts. But based on tags these are correct. Just need to correct the unread part. Please help me with this.

Please also help me with the code correction if the code is wrong by any means.

SELECT DISTINCT post.* FROM post 

INNER JOIN post_tag ON post.id = post_tag.post_id 

INNER JOIN tag 

WHERE tag.id = 21 OR tag.id = 26 OR tag.id = 63 OR tag.id = 86 OR tag.id = 11 

AND post.id != 1088 AND post.id != 338 AND post.id != 1396 

AND post.id NOT IN (SELECT post_id from user_read_activity WHERE user_id = 70)  

ORDER BY post.likes DESC LIMIT 5
Improve this question
6
  • 2
    Why not use IN for the tag ids? Like this: WHERE tag.id IN (21,26,63,86,11) Commented Dec 8, 2017 at 16:55
  • 3
    In the absence of any aggregating functions, a GROUP BY clause is, at best, misleading and, at worst, erroneous. Commented Dec 8, 2017 at 16:58
  • @Strawberry i got the same posts repeating. thats why i added GROUP BY Commented Dec 8, 2017 at 17:00
  • @SalluSalman The error is not using Group by to aggregate. You should (in some databases, must) specify how you want MySQL to handle any conflicts... using functions like MAX or MIN. Commented Dec 8, 2017 at 17:01
  • 1
    DISTINCT would return a 'correct' set of distinct results. Commented Dec 8, 2017 at 17:04

1 Answer 1

Reset to default
2

You just need brackets around your conditions to be explicit about order of precedence. You should also have a proper join between post_tag and tag:

SELECT DISTINCT post.* FROM post 

INNER JOIN post_tag ON post.id = post_tag.post_id 

INNER JOIN tag ON post_tag.tag_id = tag.id

WHERE (tag.id = 21 OR tag.id = 26 OR tag.id = 63 OR tag.id = 86 OR tag.id = 11)

AND post.id != 1088 AND post.id != 338 AND post.id != 1396 

AND post.id NOT IN (SELECT post_id from user_read_activity WHERE user_id = 70) 

ORDER BY post.likes DESC LIMIT 5

Alternatively the IN criteria advised by @dimwittedanimal would work.

SELECT DISTINCT post.* FROM post 

INNER JOIN post_tag ON post.id = post_tag.post_id 

INNER JOIN tag ON post_tag.tag_id = tag.id

WHERE tag.id IN (21, 26, 63, 86, 11)

AND post.id NOT IN (1088, 338, 1396)

AND post.id NOT IN (SELECT post_id from user_read_activity WHERE user_id = 70) 

ORDER BY post.likes DESC LIMIT 5
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

if i add tag.id IN () how will it know if the inner is AND or OR? tag.id is OR and post.id is AND. please check the query in my question
@SalluSalman, does the provided answer give you the correct result? Does it answer your question? If not, please provide some sample data and expected output data.
IN returns true if the field value matches any of the specified values (i.e. it matches the first value or the second value, etc.). NOT IN returns true if the field value does not match any of the specified values (it doesn't match the first value and it doesn't match the second value etc.).
@cf_en thanks. I used your code and returned me a different set. will verify and update if its the right result
@SalluSalman I've made an edit to fix an issue with the join on the tag table. You should use the edited version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.