Adding a count from another table to existing query

Toni Michel Caubet Published at Dev

Toni Michel Caubet

This query is working fine:

SELECT  posts.titulo as value,
                    posts.id as id,
                    posts.img_src as img,
                    posts.id_familia,
                    posts.tiempo,
                    posts.votos,
                    familias.clave,
                    familias.id as fm,
                    textos.clave,
                    textos.texto as familia,
            FROM posts,familias,textos
            WHERE posts.id_familia = familias.id AND familias.clave = textos.clave AND textos.lengua = ".detectarIdioma()." 
            and posts.id_usuario = $term 
            ORDER BY posts.id DESC

But now I would like to add how many comments has a post, which is in the comentarios table.

SELECT  posts.titulo as value,
                    posts.id as id,
                    posts.img_src as img,
                    posts.id_familia,
                    posts.tiempo,
                    posts.votos,
                    familias.clave,
                    familias.id as fm,
                    textos.clave,
                    textos.texto as familia,
                    count(comentarios.id)
            FROM posts,familias,textos
            JOIN comentarios ON comentarios.id_post = posts.id
            WHERE posts.id_familia = familias.id AND familias.clave = textos.clave AND textos.lengua = ".detectarIdioma()." 
            and posts.id_usuario = $term 
            ORDER BY posts.id DESC

The thing is that the mysql error is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) FROM posts,familias,textos JOIN comentarios ON ' at line 12

Any idea what am I missing here?

Filipe Silva

Try something like this:

SELECT posts.titulo AS value,
  posts.id AS id,
  posts.img_src AS img,
  posts.id_familia,
  posts.tiempo,
  posts.votos,
  familias.clave,
  familias.id AS fm,
  textos.clave,
  textos.texto AS familia,
  COALESCE(COM_COUNT.NUM_COMMENTS,0) AS num_comments
FROM posts 
INNER JOIN familias ON posts.id_familia = familias.id
INNER JOIN textos familias.clave = textos.clave
LEFT JOIN 
    ( SELECT id_post, COUNT(*) AS NUM_COMMENTS
      FROM comentarios
      GROUP BY id_post
    ) COM_COUNT ON COM_COUNT.id_post = posts.id   
WHERE AND textos.lengua = ".detectarIdioma()."
  AND posts.id_usuario = $TERM
ORDER BY posts.id DESC

This will left join with the number of comments per post, and if the JOIN is not matched will show 0.

Collected from the Internet

Please contact [email protected] to delete if infringement.