Mysql complicated join, anyone?

I need to write a query like this, any help please?

select id, parent, faq, 
(select count(*) from faq_table where parent = 
(select id from faq_q开发者_JS百科uestions) group by id) 
 as reply from faq_table

This table stores questions and answers (like a FAQ thing), and answers get the value of it's question's ID in the column of parent. Ideally, I would also like to add a second column called par, where all questions will get a value of 1.

Like:

id  | parent | faq
19  |    0   | name a president of the US
20  |   19   | Bush
21  |   19   | Obama
22  |    0   | Can Canada win the WC match against the Lankan's today
23  |   22   | Yes because Cheema is going to make a double today

In the resulting table out of that query, I must get:

id  | parent | faq  |  reply | par
19  |   0    | name a president of the US | 2 | 1
20  |  19    | Bush  | 0 | 0
21  |  19    | Obama | 0 | 0
22  |   0    | Can Canada win the WC match against the Lankan's today | 1 | 1
23  |  22    | Yes because Cheema is going to make a double today | 0 | 0

---

This will only work for a single hierarchy level:

SELECT t.id, t.parent, t.faq, IFNULL(g.Cnt, 0) as reply,
  g.Cnt IS NOT NULL AS par
FROM faq_table t LEFT JOIN (
    SELECT parent, COUNT(*) Cnt
    FROM faq_table
    WHERE parent > 0
    GROUP BY parent
  ) g ON t.id = g.parent

Otherwise, you can use a subquery:

SELECT t.id, t.parent, t.faq,
  (SELECT COUNT(*) FROM faq_table f WHERE f.parent = t.id) as reply,
  (SELECT COUNT(*) FROM faq_table f WHERE f.parent = t.id) > 0 As par
FROM faq_table t

---

Using your query, and this fact

1. answers get the value of it's question's ID in the column of parent

The requirement

a second column called par, where all questions will get a value of 1.

becomes trivial, and seems to confirm with your data.

select id, parent, faq, 
(select count(*) from faq_table where parent = 
(select id from faq_questions) group by id) 
 as reply,
if(parent=0,1,0) as par
from faq_table

Basically when parent=0, it is a parent. But since the comments state that there is only one table, then the above must be pseudo - the correct query should be

select id, parent, faq, 
   (select count(*) from faq_table t where t.parent = faq_table.id) as reply,
   case when parent = 0 then 1 else 0 end as par
from faq_table

---

This version also works;

SELECT q.id, q.parent, q.faq, count(a.id) AS reply, IF(ISNULL(a.id),0,1) as par
FROM faq_table q
LEFT JOIN faq_table a
  ON q.id = a.parent
  AND a.id IS NOT NULL
GROUP BY q.id

---

Give this a shot

SELECT
    f.id,
    f.parent,
    f.faq,
    (SELECT COUNT(*) FROM faq_table f2 WHERE f2.id = f.parent) AS reply
FROM faq f