CODEWITHSUNDEEP
phpsql
Ryne
Ryne

Reputation: 1415

Return one result per ChatID

I am getting data from a table and ordering them by a column in another table. Below is my query.

$query = "SELECT Chatroom.ChatID, Chatroom.User1, Chatroom.User2 FROM `Chatroom` JOIN `Messages` ON Chatroom.ChatID = Messages.ChatID WHERE User1 = ? OR User2 = ? ORDER BY Messages.MessageDate";

Say there is a chat between two people and they've messaged 10 times. I will get 10 results when really I want 1 result. I realize it's looping through messages because I joined them on chatID but I do that to get the dates.

Is there a better way to get this or is there any easy adjustment to my query that will work?

Upvotes: 1

Views: 40

Answers (1)

e-e
e-e

Reputation: 1261

If you're using MySQL, maybe try this?

SELECT c.ChatID, c.User1, c.User2
FROM Chatroom AS c
  JOIN (
    SELECT mx.*
    FROM Messages AS mx
    WHERE mx.ChatID = c.ChatID
    ORDER BY mx.MessageDate
    LIMIT 1
  ) AS m ON m.ChatID = c.ChatID
WHERE c.User1 = ?
  OR c.User2 = ?
ORDER BY m.MessageDate;

Try this instead?

SELECT c.ChatID, c.User1, c.User2
FROM Messages AS m
    JOIN Chatroom AS c on c.ChatID = m.ChatID
WHERE (c.User1 = ? OR c.User2 = ?)
GROUP BY c.ChatID
ORDER BY m.MessageDate

Upvotes: 1

Related Questions