Adding a extra subquery to MySQL query

Question

So i have 2 tables, users and messages

users table

uid, username

messages table

mid, senderid, receiverid, message, timestamp

The query i have at the moment is retrieving the uid, username and last timestamp of each chat conversation

SELECT DISTINCT 

IF (messages.senderid = '5e9b95786a71f8.25790415', messages.receiverid, messages.senderid) as uid, 

(SELECT username FROM users WHERE uid = IF (messages.senderid = '5e9b95786a71f8.25790415', messages.receiverid, messages.senderid)) as username, 

MAX(messages.timestamp) as last_timestamp

FROM messages JOIN users ON users.uid = messages.senderid WHERE messages.senderid = '5e9b95786a71f8.25790415' OR messages.receiverid = '5e9b95786a71f8.25790415'

GROUP BY 1,2

which outputs below

uid | username | last_timestamp
1 | Developer | 1601826378

2 | BetaTester | 1601826301

What i need to add to this query is the message field which is based on the last_timestamp i have in the output.

How can the query be updated to include the extra message field?

Star_Man · Accepted Answer · 2020-10-21 01:35:27Z

1

Try this.

select A.uid, C.username, B.timestamp, B.message from (SELECT DISTINCT 

IF (messages.senderid = '5e9b95786a71f8.25790415', messages.receiverid, messages.senderid) as uid,

MAX(messages.mid) as max_mid

FROM messages WHERE messages.senderid = '5e9b95786a71f8.25790415' OR messages.receiverid = '5e9b95786a71f8.25790415'

GROUP BY 1) A join messages B on A.max_mid = B.mid join users C on A.uid = C.uid

edited Oct 21, 2020 at 1:35

answered Oct 21, 2020 at 1:22

Star_Man

1,0911 gold badge14 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TRicks Over a year ago

Thanks but when i tried this i get "Can't group on 'max_mid'"

Star_Man Over a year ago

@TRicks, Updated my answer

T. Peter · Accepted Answer · 2020-10-21 01:35:52Z

0

I'm a little confuse, since you already got last_timestamp why don't just left join message table again.

left JOIN messages m2 on last_timestamp = m2.timestamp and mid = m2.mid

and just select message column

select m2.message

edited Oct 21, 2020 at 1:35

answered Oct 21, 2020 at 1:25

T. Peter

8856 silver badges13 bronze badges

Collectives™ on Stack Overflow

Adding a extra subquery to MySQL query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related