mysql - Get unique records based on multi-join with conditionals -

i have 4 tables: posts, users, mentions, following

posts ---------------------------- id  |  user_id  |  post_text 1      1           foo 2      1           bar 3      2           hello 4      3           jason  users ------------ id  |  name 1      jason 2      nicole 3      frank  mentions -------------------------- id  |  post_id  |  user_id 1      4           1   following ------------------------------------------------- id  |  user_id  |  user_id_of_user_being_followed 1      1           2 

posts includes user_id of user posted text users has user id , name of user mentions has post id , user id of post has mentioned 1 or more other users following has user id , user following (user can follow 0 many users)

what i'm trying return posts users given user follows, plus posts have mentioned user (whether or not given user following), without returning duplicates.

select p.id, p.post, u.name,  following f  join posts p on f.following = p.user_id join users u on u.id = p.user_id  f.user_id = :user; 

the above returns posts users given user following, i'm struggling figuring out how include mentions (remember, user not have follow able see post they've been mention in).

update: john r able figure out:

select distinct(p.id), p.post, u.name  posts p  left join following f on f.following = p.user_id left join mentions m on m.posts_id = p.id  join users u on u.id = p.user_id (f.user_id = :user_id or m.user_id = :user_id) 

if understand querstion correctly want left join include mentions.. not filter out followers/posts

if can add sample data play can make sure working how want to...

select      if(p.id not null, p.id, p1.id) post_id,      if(p.post not null, p.post, p1.post) post_text,      u.username, m.id, m.user_id posts p join users u on u.id = p.user_id join following f on f.user_id_of_user_being_followed = u.id left join mentions m on m.user_id = f.user_id left join posts p1 on p1.id = m.post_id f.user_id = :user or m.user_id = :user; 

i left join mentions post made , when user_id in mention table equal specified user filter out other users. left join shouldn't change number of rows returned.. include mentions

---

edit: working fiddle

after playing around realised trying put of data 1 row.. try this:

(     select p.id, p.post_text, u.name     posts p     join users u on u.id = p.user_id     join following f on f.user_id_of_user_being_followed = u.id     f.user_id = 1 ) union (     select p.id, p.post_text, u.name     following f     join mentions m on m.user_id = f.user_id     join posts p on p.id = m.post_id     join users u on u.id = p.user_id     f.user_id = 1 );