pankaj pankaj - 5 months ago 6
SQL Question

Error in SQL when using coalesce to get number of items(comments) related to a post

I am working on a SQL where I need to get number of comments on every post. I have three tables to use here users, posts and comments. posts and comments have postid as foreign key.

I am getting following error when I tried to get the comments count:


#1064 - 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, users where users.userid = posts.userid and posts.userid = 16 order ' at line 1


My SQL:

SELECT
postid,
post,
type,
users.userid,
CONCAT( firstname, ' ', lastname ) as name,
COALESCE(
(SELECT
COUNT(*)
FROM comments
WHERE postid = posts.postid
),
0) AS cnt,
FROM
posts,
users
where
users.userid = posts.userid and
posts.userid = 16
order by
postid desc

Answer

First, learn to use proper JOIN syntax. Never use commas in the FROM clause.

Second, table aliases and qualified column names make a query easier to write and to read.

SELECT p.postid, p.post, p.type, u.userid,
       CONCAT_WS(' ',  u.firstname, u.lastname ) as name,
       (SELECT COUNT(*) FROM comments c WHERE c.postid = p.postid) AS cnt
FROM posts JOIN
     users u
     ON u.userid = p.userid and p.userid = 16
ORDER BY p.postid desc;

(I am guessing at the tables where the columns come from.(

In addition:

  • The COALESCE() is unnecessary. COUNT(*) does not return NULL.
  • The extra comma was the specific problem in your query.
  • If firstname is missing, then your version will have name start with a space. CONCAT_WS() fixes this problem.
Comments