MySql - 根据匹配的ID获取行

Question

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.

Answer 1

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)