Remove duplicate lines in a Mysql query

Asked

Viewed 856 times

0

I need to do an SQL query where returns the last messages exchanged between users (my account for example) and who I talked to... regardless of whether the last conversation was sent or received.

The code I am using behind the sent and received in the case repeating the user, I would like to know how I do not repeat group only my last conversation with whom I talked independently if the message was sent or received.

SELECT distinct * FROM mensagens inner join usuarios on de = id_user where para LIKE '$sessao' 
union SELECT distinct * FROM mensagens inner join usuarios on para = id_user where de LIKE '$sessao' 
group by id_user  order by  id asc

Table messages:

id - de - para - mensagem

Table users:

id_user - nome
php mysql

  • In $sessao receives its own ID?

  • Jonatan, it’s nice to indent SQL like @Jader does in the answer below, it’s much easier to understand what is happening.

2 answers

1

You can search for the last messages when de or para are equal to the desired ID, sort by decreasing message ID (consider creating a date and time field) and do two left joins to pick up the names de and para, thus:

Example to get the last messages from user ID 1:

SELECT m.id, de.nome de, para.nome para, m.mensagem
FROM mensagens m
LEFT JOIN usuarios de ON de.id_user = m.de
LEFT JOIN usuarios para ON para.id_user = m.para
WHERE m.de = '1' OR m.para = '1' ORDER BY m.id DESC;

See working on Sqlfiddle

I reread your question more carefully and follow below how to get the last exchange conversation with the other person, regardless of whether it was received or sent:

Now assuming the logged in user is ID 4:

SELECT m.id, de.nome de, para.nome para, m.mensagem
FROM mensagens m
LEFT JOIN usuarios de ON de.id_user = m.de
LEFT JOIN usuarios para ON para.id_user = m.para
WHERE
(m.de = (SELECT if (de = '4', para, de) FROM mensagens WHERE de = '4' or para='4' ORDER BY id DESC LIMIT 1) 
and
m.para = '4')
or
(m.de = '4'
and
m.para = (SELECT if (para = '4', de, para) FROM mensagens WHERE de = '4' or para='4' ORDER BY id DESC LIMIT 1)
) ORDER BY m.id ASC;

Obs.: Trade all the '4' by the id of the logged in user.

See working on Sqlfiddle

0

Puts a date and/or time field in the messages table , and puts a LIMIT 1 when making a SELECT in the messages

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.