MYSQL Inner Join (If statement in SQL code?)

Question

I have two sql tables: messages and users.

messages = 
   message_id, 
   sender_id, 
   recipient_id, 
   conversation_id, 
   message, 
   datetime, 
   read_at

users = 
   user_id, 
   name, 
   lastname, 
   ...

Now I would like to get (select) per conversation_id just the newest message like that:

date(1 latest) - conversation partnerXY - newest message
date(2 latest) - conversation partnerZY - newest message

...

This I have already found out:

SELECT     
  messages.message_id, 
  messages.sender_id, 
  messages.recipient_id, 
  messages.datetime, 
  messages.message, 
  messages.read_at, 
  users.name, 
  users.lastname      
FROM     
  messages, 
  users    
WHERE      
  (messages.recipient_id = $r_id OR  messages.sender_id = $s_id)
AND    
  messages.sender_id = users.user_id    
GROUP BY     
  messages.conversation_id 
ORDER BY 
  messages.datetime DESC

But now the name and lastname can also be my name and I would like to get the name of the opposite. In the session the current user id is saved. Would be very nice of you!

Answer 1

I am not 100% sure what you are asking for, but if you are not sure which join type to use, please refer to this image:

If you need to do IF-ELSE checks you can do CASES like this:

CASE
  WHEN COLUMN1 LIKE 'Bill' THEN 'Name is Bill'
  WHEN COLUMN1 LIKE 'James' THEN 'Name is James'
  WHEN COLUMN1 IS NULL THEN 'Name is not there'
  ELSE COLUMN2
END AS RESULT_NAME

Answer 2

I'm not exactly sure if I understand what you're asking, but it sounds like you'd like to select both the sender and recipient name for any given message? Try something like:

SELECT 
m.message_id, 
m.message, 
s.name AS sender_name, 
s.lastname AS sender_lastname, 
r.name AS recipient_name, 
r.lastname AS recipient_lastname

... more columns ...

FROM messages m
LEFT JOIN users s ON m.sender_id = s.user_id
LEFT JOIN users r ON m.recipient_id = r.user_id

... more conditions ...