I am having a problem with the next step of my query, my mind is completely drawling a blank here….I have 3 MySql tables.
The first table is called post and has an id column (there are other columns, but they are not relevant)
Id Title
---- --------
1 A
2 B
The second table is called followingPost (posts users like) and it has an id column, userid column and postid column
Id userid postid
---- -------- -------------------
1 1 2
3 2 1
The third table is called postTags (tags for the posts) and this has an id column, postid column and tagid column.
Id postid tagid
---- -------- --------
1 2 1
2 1 1
Get Users Liked Posts, which I have done with the following
SELECT *
FROM followingPost
INNER JOIN posts ON posts.id = followingPost.postid
WHERE userid = 14
And Then Get Users Liked Posts with Tag IDs, , which I have done with the following
SELECT *
FROM followingPost
INNER JOIN posts ON posts.id = followingPost.postid
INNER JOIN postTags ON posts.id = postTags.postid
WHERE userid = 14
No what I am trying to do is take the tags of the post the user liked and get all the posts that also have the same tag id.
This is what I am expecting:
Id Title
---- --------
2 B
I am expecting this because user 1 likes post 2 and post 2 has tag of 1 and so does post 1, so I am expecting to return post 1
Any ideas on how to accomplish the final step?
I really hope this makes sense, please let me know if you need me to clear anything up.
select * from posts
left join postTags on posts.id = postTags.postid
where postTags.tagid in
(SELECT postTags.tagid
FROM followingPost
INNER JOIN posts ON posts.id = followingPost.postid
INNER JOIN postTags ON posts.id = postTags.postid
WHERE userid = 14)
and posts.id not in
(SELECT posts.id
FROM followingPost
INNER JOIN posts ON posts.id = followingPost.postid
INNER JOIN postTags ON posts.id = postTags.postid
WHERE userid = 14)