Adding a count from another table to existing query

Question

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 · Accepted Answer · 2013-11-14 22:52:53Z

1

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.

answered Nov 14, 2013 at 22:52

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gerrat · Accepted Answer · 2013-11-14 22:49:59Z

0

Try 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,
        count(comentarios.id)
FROM posts INNER JOIN familias ON posts.id_familia = familias.id 
INNER JOIN textos ON familias.clave = textos.clave 
LEFT OUTER JOIN comentarios ON comentarios.id_post = posts.id
WHERE textos.lengua = ".detectarIdioma()." 
AND posts.id_usuario = $term 
GROUP BY posts.titulo,
        posts.id,
        posts.img_src,
        posts.id_familia,
        posts.tiempo,
        posts.votos,
        familias.clave,
        familias.id,
        textos.clave,
        textos.texto
ORDER BY posts.id DESC

You were just mixing two types of JOIN syntax. ...and you'll probably need to group by every column except the one you're counting.

EDIT: To not restrict your results to only those with comments, you'll need to do a LEFT OUTER JOIN on that table.

edited Nov 14, 2013 at 22:49

answered Nov 14, 2013 at 22:43

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

2 Comments

Toni Michel Caubet Over a year ago

Hi there, thanks for your time. Like this Query is working now, problem is I didn't think this through and is only returning posts with comments, I would like comments not to filter the query, just add the count of comments of each post, is that tricky?

Gerrat Over a year ago

I updated the sql (you may or may not need the group by - not sure about mysql)

Big Money · Accepted Answer · 2013-11-14 22:51:59Z

0

You have a comma before the FROM in the first query but not before the FROM in the second query

answered Nov 14, 2013 at 22:51

Big Money

10.3k6 gold badges29 silver badges39 bronze badges

Collectives™ on Stack Overflow

Adding a count from another table to existing query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related