user6256769 user6256769 - 1 year ago 54
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download