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
CONCAT( firstname, ' ', lastname ) as name,
WHERE postid = posts.postid
0) AS cnt,
users.userid = posts.userid and
posts.userid = 16
First, learn to use proper
JOIN syntax. Never use commas in the
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.(
COUNT(*)does not return
firstnameis missing, then your version will have
namestart with a space.
CONCAT_WS()fixes this problem.