phpmyadmin Question posted in
The official documentation can be found here.

How to get a row count of another table, using SELECT column as a WHERE clause using MySQL – Phpmyadmin

I’m selecting posts from a ‘posts’ table in a MySQL database, but I want one of the columns returned to be a comment count from a comments table using the rows selected post id. My DB is relational so the comment table has a post id field (comment_post_id). Here’s what I have, but this query is creating a syntax error in PHPMYAdmin:

SELECT post_id, post_title, post_status, post_image, post_tags, post_views, post_date, user_name, cat_title, 
COUNT(SELECT comment_id FROM comments WHERE comment_post_id = post_id) AS comment_count
FROM posts 
LEFT JOIN users ON post_author = user_id 
LEFT JOIN categories ON post_category_id = cat_id
ORDER BY post_id DESC

Post and Comments table columns are laid out as follows:

Post:
post_id, post_title, post_status, post_image, post_tags, post_views, post_date, post_author, post_category_id

Comments:
comment_id, comment_post_id, comment_content

Tags:

2

Answers


  1. 0

    You could join comments with post then use count(comment_id). Instead of redundant sub query. 

    SELECT 
    post_id,
    post_title,
    post_status,
    post_image,
    post_tags,
    post_views,
    post_date,
    user_name,
    cat_title,
    COUNT(comment_id) AS comment_count
FROM
    posts
        LEFT JOIN
    users ON post_author = user_id
        LEFT JOIN
    categories ON post_category_id = cat_id
        JOIN
    comments ON post_id = comment_post_id
ORDER BY post_id DESC
    Login or Signup to reply.
  2. 0

    You can use this query to get the count here. Try this way

    SELECT post_id, post_title, post_status, post_image, post_tags, post_views, post_date, user_name, cat_title, 
(SELECT COUNT(comment_id) FROM comments WHERE comment_post_id = post_id) AS comment_count
FROM posts 
LEFT JOIN users ON post_author = user_id 
LEFT JOIN categories ON post_category_id = cat_id
ORDER BY post_id DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search