vendredi 13 février 2015

Better/Right way to write a complex query



This is my table.



SELECT * FROM [Message]


Message Table Data


Now what I want is, I want the list of only last message which User with Id: 101 has sent or received from any other user. The query which I wrote for it is below



SELECT
(SELECT TOP 1 [Message_id]
FROM [Message]
WHERE
([Sender_id] = REC.[Sender_id] AND [Receiver_id] = REC.[Receiver_id])
OR
([Sender_id] = REC.[Receiver_id] AND [Receiver_id] = REC.[Sender_id])
ORDER BY 1 DESC) AS [Message Id],
REC.[Sender_id] AS [Sender Id],
REC.[Receiver_id] AS [Receiver Id],
(SELECT TOP 1 [Message]
FROM [Message]
WHERE
([Sender_id] = REC.[Sender_id] AND [Receiver_id] = REC.[Receiver_id])
OR
([Sender_id] = REC.[Receiver_id] AND [Receiver_id] = REC.[Sender_id])
ORDER BY 1 DESC) AS [Message]
FROM
(SELECT DISTINCT [Sender_id], [Receiver_id]
FROM [Message]
WHERE [Sender_id] = '101') REC


And I am getting the following result which seems fine.


Query Results


I'm new to DB queries and it seems that my query is very inefficient and long. Can anyone please suggest a better way to write this query? Also, using JOINS if that might be a better possible way to write this query.


Thanks.


Aucun commentaire:

Enregistrer un commentaire