user6256769 user6256769 - 5 months ago 6
SQL Question

sql - select data from 2 tables

I have 2 tables - chats_topices and chats_replies

I would like to select all chats_topices fields + the field createDate (timestamp) from chats_replies which closest to the current date.

chats_replies contain field CHATID that connect between the tables

$sql = "SELECT *
FROM `chats_topics` AS topics, `chats_replies` AS replies
WHERE (".$search.") AND
(topics.id = replies.chatID) AND
(XXXX)
ORDER BY topics.createDate DESC";

Answer

It's bad practice to use SELECT * in live code, I'd go with something like this;

SELECT topics.Field1, topics.Field2, MAX(replies.createDate) createDate
FROM `chats_topics` AS topics
LEFT JOIN `chats_replies` AS replies
ON topics.id = replies.chatID
WHERE (".$search.")
    AND (topics.id = replies.chatID)
    AND (XXXX)
GROUP BY topics.Field1, topics.Field2
ORDER BY topics.createDate DESC

Add in your fields that you need to the select and also add them in the group by.