Problem with logic when using LEFT JOIN

Question

Problem with logic when using LEFT JOIN

Navigation

#1 by (4 votes)

7

In my system, there is a system of postings and another system of friendships. Here is the structure of the tables:

posts : id | usuario | conteudo | data | hora

Friendships : id | usuario1 | usuario2 | status

And I'm using the following query to return the posts of 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 logged-in and my friends posts (relationship between users where status = 2 ), and here's an example of data contained in the amizades table:

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

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

But since there is my user (Igor) in the two rows of the table, it duplicates the posts of that user, that is, if there were another row in the table, which would make the friendship relationship between my user and another would triple the posts. How do I resolve this?

php mysql join

asked by anonymous 21.11.2015 / 16:50

1 answer

Can you optimize this code? How to capture the first letter of a String name and if the letter is 'C' save the name in a vector?

score 4 · Accepted Answer

You have a query here 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 above does not 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 the Fiddle

Another, conceptually simpler way of solving the problem, though more extensive, would be to divide the results into two: Your posts and the posts of your friends. This could be done as well

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

Regarding your questions:

Quoted descriptions are just a visual aid to understanding the source of posts. It can be removed from the query.
The idea is to combine your posts with the posts of your friends through the use of UNION ALL - Combine the result of two queries
The way you have written 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