I've a following query to select data from a table (chat) in a chat system:
SELECT * FROM (SELECT * FROM chat WHERE id_chat = '$chat_id' ORDER BY id DESC LIMIT 10) S
WHERE id_chat = '$chat_id'
ORDER BY id ASC
LIMIT 10
In this table (chat), there is a user column where it is the user id that sent the message.
I would, from the id_from of the user who sent the message, get back data that user (users table).
Table chat:
id | id_chat | id_from | id_to | message | date
1 | 23 | 1 | 2 | hello! | 01-12-2016
Table users:
id | name | photo
1 | John | pic.png
2 | Nick | hey.jpg
What better way to do it using the above query? LEFT JOIN? INNER JOIN? And, how do?