CODEWITHSUNDEEP
mysqlsql
Viteazul
Viteazul

Reputation: 181

Using select in where clause

I have a table named message. I am trying to select the message from the table only when person_send_id and person_receive_id are distinct.
I am able to select person_send_id and person_receive_id correctly.
However, I don't know how to select the message.
I was thinking of using a Select within a Select but I don't know how to implement it or if its the right way of doing it.

SELECT DISTINCT person_send_id, person_receive_id 
FROM `message` 
WHERE person_send_id = 21 OR person_receive_id = 21 
//AND SELECT message FROM `message` when these criteria are met

Message Table

message_id 
message 
person_send_id 
person_receive_id

Here is my table data.

enter image description here

I want to select only the first message where person_send_id and person_receive_id are repeating themselves.
So if I send 10 messages to the same person, I only want to grab only record with the message, in this case "Hello" for example

Upvotes: 4

Views: 90

Answers (1)

Robin Carlo Catacutan
Robin Carlo Catacutan

Reputation: 13679

You can just add the message to your current select.

SELECT DISTINCT person_send_id, person_receive_id, message 
FROM `message` 
WHERE person_send_id = 21 OR person_receive_id = 21;

And I assume you'll be wanting the latest one since it will be acting as a title.

Add ORDER BY message_id DESC LIMIT 1

e.g.

 SELECT DISTINCT person_send_id, person_receive_id, message 
 FROM `message` 
 WHERE person_send_id = 21 OR person_receive_id = 21 
 ORDER BY message_id DESC LIMIT 1;

Upvotes: 5

Related Questions