php - MySQL如何使用不同的约束查询多个表？

Question

如何使用不同的约束查询多个表？例如，将第一个表中的结果限制为 1 行，但从第二个表中获取所有结果，然后按唯一 ID ASC 对这些结果进行排序。

例如，像这样:

SELECT p.entry_id AS post_id,
       p.topic AS post_topic,
       p.body AS post_body,
       r.reply AS post_reply
FROM 
#should get only one row
(SELECT entry_id, topic, body FROM entry_posts WHERE entry_id = {$id} LIMIT 1) AS p

FULL JOIN
#should get all rows with this entry_id and order them
(SELECT reply, FROM entry_replies WHERE entry_id = {$id} ORDER BY id ASC) AS r

ON p.entry_id = r.entry_id

在本例中，两个表都有一个名为entry_id 的列，其中包含相同的值。

Answer 1

SELECT p.entry_id AS post_id,
       p.topic AS post_topic,
       p.body AS post_body,
       r.reply AS post_reply
FROM entry_posts p
LEFT OUTER JOIN entry_replies r ON r.entry_id = p.entry_id
WHERE p.entry_id = {$id}
ORDER BY r.id