Question:

Currently I'm using the following query to select all posts saved in the database: "SELECT * FROM postagens ORDER BY id DESC LIMIT 7" , however, I want to select only posts made by "friends".

The friendships list is in the amizades table, where when they are friends, the status = 2 . How to relate both tables to select all posts made by "friends"?

What I got so far:

SELECT
   postagem.id, postagem.usuario, postagem.conteudo, postagem.data, postagem.hora,
   amizade.usuario1, amizade.usuario2, amizade.status
FROM
   postagens postagem
LEFT JOIN
   amizades amizade ON postagem.usuario = amizade.usuario2
WHERE
   amizade.status = 2
ORDER BY
   postagem.id DESC
LIMIT 10

However, I want to select all posts made by me and also by my friends (when amizade.status = 2 ), and then I'm failing, I don't know how to select only mine and my friends' posts too, because only mine appear .

Columns:

posts :

id | usuario | conteudo | data | hora

friendships :

id | usuario1 | usuario2 | status

Answer:

Follow what you want

Creation

create table postagens (
  id int primary key auto_increment,
  usuario int,
  conteudo text);
  
create table amizades (
  id int primary key auto_increment,
  usuario1 int,
  usuario2 int,
  status int);
  
insert into postagens (usuario, conteudo) values
(1, 'aksdjasdkasdj'),
(2, 'ja-s09920masd'),
(1, '90123091231092'),
(3, 'Nao devo ser exibidor');

insert into amizades (usuario1, usuario2, status) values
(1, 2, 2);

search the data

SELECT
        p.*,
        case when p.usuario = a.usuario1 then 'eu' else 'amigo' end as quem_enviou
FROM postagens p
INNER JOIN amizades a ON p.usuario = a.usuario1 OR p.usuario = a.usuario2
where (a.usuario1 = 1 or a.usuario2 = 1) and a.status = 2;

Where is a.usuario1 = 1 or a.usuario2 = 1 put the ID of the user you want to fetch the messages.

If you want to fetch only from friends, remove the a.user1 part. If you want to fetch only yours, remove the a.usuario2 part.