Remove duplicate rows in a MySQL query

Navigation
0

I need to do a SQL query where I return 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 that I am using sends the sent and received in the case repeating the user, I would like to know how I do not repeat grouping only my last conversation with whom I spoke 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

Messages table: 

id - de - para - mensagem

Users table: 

id_user - nome
    
asked by anonymous 13.09.2014 / 15:56

2 answers

1

You can fetch the last messages when de or para are the same as the desired ID, sort by descending message ID (consider creating a date and time field) and do two left joins to get the names de and para , like this:

Example to get the latest 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 in SQLFiddle

I relay your question more closely and follow below how to get the last conversation exchanged 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;

Note: Change all '4' to the id of the logged in user .

See working in SQLFiddle

    
13.09.2014 / 18:14
0

Places a date and / or time field in the messages table, and places a LIMIT 1 when it will SELECT in messages

    
13.09.2014 / 17:07
Problem displaying image Programming error C, how to make it work