CODEWITHSUNDEEP
phpmysqlsqlcountsubquery
New coder
New coder

Reputation: 33

MYSQL count subquery on current row

I am having trouble with this query and am hoping someone can help.

  SELECT 
    myTable.id,
    myTable.subject,
    myTable.upvotes,
    myTable.downvotes,
    (SELECT COUNT(id) 
     FROM myTable 
     WHERE myTable.thread = myTable.id) AS comments_count 
  FROM myTable

Basically I have a table with posts and comments, the comments thread is tied to the id of the original post. In my query I want to show how many relpies (how many threads = id) from all rows for the current id/row.

I hope that makes sense :)

Upvotes: 2

Views: 2555

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You need to specify the the table with new alias to match in your subquery other wise it will match the thread with id from same table 

SELECT 
m.id,
m.subject,
m.upvotes,
m.downvotes,
(SELECT COUNT(id) 
  FROM myTable 
  WHERE myTable.thread = m.id) AS comments_count 
FROM myTable m

Or Better to use LEFT JOIN

SELECT 
    m.id,
    m.subject,
    m.upvotes,
    m.downvotes,
    COUNT(mm.id) AS comments_count 
FROM myTable m 
  LEFT JOIN  myTable mm ON(mm.thread = m.id)
 GROUP BY m.id

Upvotes: 3

Related Questions