CODEWITHSUNDEEP
mysql
BMB08
BMB08

Reputation: 13

In MySQL I want to bring private posts if they are my friends and all public posts

could someone help me please? I want to bring the publications of a user by their id, bring the private ones if they are my friends and all the public publications I have the following tables. Users:

id_user email password
1 Brayan ...
2 Berenice ...
3 Joe ...

Friends:

id_friend id_user(is FK to users) id_user_2(is FK to users)
1 1 2

and publications:

id_publication id_user(is FK to users) message is_public
1 1 I am happy today 0
1 2 This is public 1
1 2 This is not public 0
1 3 I am happy today 1

Upvotes: 0

Views: 64

Answers (1)

Ahmed Mamdouh
Ahmed Mamdouh

Reputation: 26

Note that in my solution i assumed id_user_2 column is your friend user id.

SELECT * 
FROM publications 
WHERE is_public = 0 AND 
(id_user IN (SELECT id_user_2 FROM friends WHERE id_user = 1) OR id_user IN (SELECT id_user FROM friends WHERE id_user_2 = 1)) OR 
id_user = 1;

Hope you find this solution helpful.

Upvotes: 1

Related Questions