Problem with logic when using LEFT JOIN

Question

In my system, there is a system of posts and another of friendships. It follows the structure of tables:

posts: id | usuario | conteudo | data | hora

friendships: id | usuario1 | usuario2 | status

And I’m using the following query to return posts from both the current user and my friends (when status = 2):

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

It’s working perfectly, returning both my posts (logged-in user) and those of my friends (user-to-user relationship where status = 2), and here is an example of data contained in the table amizades:

2 | Igor  | Joao | 2
1 | Lucas | Igor | 0

In the above data, when status = 2 means they’re friends, and when status = 0 means they’re not friends.

However, as there is my user (Igor) in the two rows of the table, it duplicates the posts of such user, that is, if there was another row in the table, of which would make the relationship of friendship between my user and any other, would triple the posts. How to solve this?

Answer 1

Here is a query that should answer your problem.

SELECT  DISTINCT postagem.id, 
        usuario, 
        conteudo, 
        data,
        hora
FROM postagens postagem
LEFT JOIN 
(
    SELECT usuario1, usuario2
     FROM amizades
    WHERE status = 2
      AND ( usuario1 = 'Igor' or usuario2 = 'Igor')
) amizades
  ON amizades.usuario1 = postagem.usuario
  OR amizades.usuario2 = postagem.usuario
WHERE amizades.usuario1 IS NOT NULL
   OR postagem.usuario = 'Igor'
ORDER BY postagem.id DESC
LIMIT 10

If the last one doesn’t work, do it this way:

SELECT  postagem.id, 
        usuario, 
        conteudo, 
        dt
FROM postagens postagem
LEFT JOIN 
(
    SELECT usuario1    id_amigo
     FROM amizades
    WHERE status = 2
      AND usuario2 = 'Igor'
    UNION
    SELECT usuario2
     FROM amizades 
    WHERE status = 2
      AND usuario1 = 'Igor'
) amizades
  ON amizades.id_amigo = postagem.usuario
WHERE amizades.id_amigo IS NOT NULL 
   OR postagem.usuario = 'Igor'
ORDER BY postagem.id DESC
LIMIT 10

Stay here the Fiddle

Another way, conceptually simpler to solve the problem even more extensive, would be to divide the results in two: Your posts and your friends' posts. This could be done

SELECT postagem.id, 
       usuario, 
       conteudo, 
       data, 
       hora, 
       'Minha postagem' AS Descricao
FROM postagens postagem
WHERE usuario = 'Igor'

UNION ALL

SELECT postagem.id,
       usuario, 
       conteudo, 
       data, 
       hora, 
       'Postagem de um amigo'
FROM postagens postagem
INNER JOIN 
(
    SELECT usuario1 id_amigo
    FROM   amizades
    WHERE status = 2
      AND usuario2 = 'Igor'

    UNION

    SELECT usuario2
    FROM   amizades
    WHERE status = 2
      AND usuario1 = 'Igor'

) amizades
   ON id_amigo = postagens.usuario
ORDER BY postagem.id DESC
LIMIT 10

In relation to your questions:

• Quotes descriptions are just a visual help to understand the origin of the posts. It can be removed from the query.
• The idea is to combine your posts with your friends' posts through the use of UNION ALL - Combine the result of two queries
• The way you wrote your query, there is no difference between LEFT JOIN and INNER JOIN.

SELECT 
    postagem.id, usuario, conteudo, data, hora, usuario1, usuario2, status
FROM 
    postagens postagem
LEFT JOIN
    amizades ON usuario = usuario1 OR usuario = usuario2
WHERE
status = 2   <- Esta condição obriga a que exista um registo na tabela amizades sendo desta forma equivalente ao INNER JOIN