¿Cómo puedo escribir una consulta sql equivalente con join?

Consulta SQL crudo:

SELECT * FROM (SELECT p.id, p.title, p.mark, (SELECT max(created) FROM comments c WHERE c.post_id=p.id AND c.mark=1) AS latest_at FROM posts p) AS Post WHERE Post.latest_at IS NOT NULL ORDER BY latest_at DESC LIMIT 10 

Estoy intentando escribir una consulta SQL equivalente con join. ¿Cómo puedo hacerlo?

 mysql> describe posts; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | NO | | NULL | | | title | varchar(255) | NO | | NULL | | | body | text | YES | | NULL | | | category_id | int(11) | YES | | NULL | | | tags | varchar(50) | YES | | NULL | | | mark | tinyint(4) | NO | | 1 | | | created | datetime | YES | | NULL | | | modified | datetime | YES | | NULL | | +-------------+--------------+------+-----+---------+----------------+ mysql> describe comments; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | post_id | int(11) | NO | MUL | NULL | | | name | varchar(255) | NO | | NULL | | | email | varchar(255) | NO | | NULL | | | body | varchar(500) | NO | | NULL | | | mark | tinyint(4) | NO | | 1 | | | created | datetime | YES | | NULL | | | modified | datetime | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+ 

Cualquier respuesta será muy apreciada. Gracias por adelantado.

Prueba esto:

 SELECT p.id, p.title, p.mark, c.latest_at FROM posts p LEFT JOIN ( SELECT post_id, MAX(created) AS latest_at FROM comments WHERE mark = 1 GROUP BY post_id ) AS c ON c.post_id = p.id WHERE c.latest_at IS NOT NULL ORDER BY c.latest_at DESC LIMIT 10 

o solo esto:

 SELECT p.id, p.title, p.mark, c.latest_at FROM posts p INNER JOIN ( SELECT post_id, MAX(created) AS latest_at FROM comments WHERE mark = 1 GROUP BY post_id ) AS c ON c.post_id = p.id ORDER BY c.latest_at DESC LIMIT 10 

porque el c.latest_at IS NOT NULL de la cláusula WHERE de la primera consulta convierte a LEFT JOIN en INNER JOIN .

Yo sugeriría que:

 SELECT p.id, p.title, p.mark, c.latest_at FROM posts p LEFT OUTER JOIN (select post_id, max(created) latest_at from comments where mark=1 group by post_id ) c ON (c.post_id=p.id) WHERE c.latest_at IS NOT NULL ORDER BY c.latest_at DESC LIMIT 10; 

No veo una razón para reescribir esa consulta. La subconsulta correlacionada debe tener un buen performance. Pero, puedes hacer:

 SELECT p.id, p.title, p.mark, c.maxc FROM posts p JOIN (SELECT post_id, MAX(created) as maxc FROM comments WHERE mark = 1 GROUP BY post_id ORDER BY maxc DESC LIMIT 10 ) c ON c.post_id = p.id ORDER BY maxc DESC; 

Notas:

  • La subconsulta correlacionada ahora es una consulta de agregación.
  • Esto usa JOIN lugar de LEFT JOIN porque (presumiblemente) solo quieres filas que tengan un comentario.
  • El LIMIT está en la subconsulta, por lo que JOIN es más eficiente.

Creo que una expresión de tabla común también funcionaría (no estoy seguro del performance):

 WITH cte_latest (id, maxCreated) AS ( SELECT post_id, MAX(created) FROM comments WHERE mark = 1 GROUP BY post_id ) SELECT * -- Whetever columns you need here FROM posts p INNER JOIN cte_latest ON p.id = cte_latest.id