php - MYSQL count subquery on current row -

i having trouble query , hoping can help.

  select      mytable.id,     mytable.subject,     mytable.upvotes,     mytable.downvotes,     (select count(id)       mytable       mytable.thread = mytable.id) comments_count    mytable 

basically have table posts , comments, comments thread tied id of original post. in query want show how many relpies (how many threads = id) rows current id/row.

i hope makes sense :)

you need specify the table new alias match in subquery other wise match thread id same table

select  m.id, m.subject, m.upvotes, m.downvotes, (select count(id)    mytable    mytable.thread = m.id) comments_count  mytable m 

or better use left join

select      m.id,     m.subject,     m.upvotes,     m.downvotes,     count(mm.id) comments_count  mytable m    left join  mytable mm on(mm.thread = m.id)  group m.id