syam deth syam deth - 13 days ago 6
MySQL Question

Mysql join query for showing friends posts with viewstatus!=public

I have three table tbl_user, tbl_posts, tbl_friends

tbl_user stores all the user datas.

tbl_friends stores which user are friends with other.

tbl_posts
, where the users posts are stored.
userid
is the field where all the users id are stored.
p_viewstatus
field stores the view status of the posts. if the view status of the my friends posts is only by me then that posts should not shown to other users

If sam have a post with id=40 in
tbl_posts
. The
p_viewstatus
of the post is only by me then that post must only be visible to sam only.

If sam have another post with id=41 and
p_viewstatus
!='only by me'
this post must visible to all users who are sam's friend

Tried the following query but don't know how to relate the p_viewstatus

Note: I want to fetch all of my posts and my friends posts from **
tbl_posts
except the
p_viewstatus
of my friends posts no equal to 'only by me'**

SELECT * FROM `tbl_posts` as p,`tbl_friends` as f WHERE p.`userid`=f.`userid` and p.`userid`=23


Thanks in advance.

enter image description here

enter image description here

enter image description here

BELOW SCREENSHOT SHOWS THE TABLE CONTENTS AFTER JOIN QUERY.
enter image description here

Answer

Here's how your query should look like:

SELECT * FROM `tbl_posts` as p 
WHERE p.`userid`=23  OR (p.`userid` IN (SELECT `friendsid` `FROM tblfriends` WHERE `userid`=23)
AND p.`p_viewstatus`<>'only by me')

First you select the data from the table you need. After that you filter it with the user id and the ids of the friends of the user and you also tell it that you only want the posts that are not viewable only by me.