Hezerac Hezerac - 2 months ago 8
SQL Question

MySQL How to query multiple tables using different constraints?

How to query multiple tables using different constraints?
For example, limiting results to 1 row from the first table, but getting all results from the second and then ordering those results by unique id ASC.

For example, something like this:

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


In this case both tables have a column called entry_id that contain the same values.

Answer
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