CODEWITHSUNDEEP
phpsql
user6256769
user6256769

Reputation: 21

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";

Upvotes: 0

Views: 38

Answers (2)

Kenny Grage
Kenny Grage

Reputation: 1124

Rich Benner gave you a good join statement for this particular problem. I would like to follow up and attach an image I have found useful when making join statements in the hopes that it might help in the future. I don't remember where I got this picture (although you can see a copyright on the bottom).

enter image description here

Upvotes: 1

Rich Benner
Rich Benner

Reputation: 8113

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.

Upvotes: 1

Related Questions