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 | 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 |
CodePudding user response：
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 = 1;
Hope you find this solution helpful.