counting from different tables

[BB101]

I have a problem getting counts from 2 different tables, via joins from the parent table.

At the moment, I am doing this:

SELECT q.*, q.id as q_id, t.town, c.category,
  count(qa.id) AS answers
FROM qq_local q
  LEFT JOIN qa_local qa
    ON q.id = qa.q_id
      AND qa.active = 1
  LEFT JOIN towns t
    ON t.id = q.town_id
  LEFT JOIN q_category c
    ON q.category_id = c.id
  GROUP BY q.q
  ORDER BY q.q_id DESC

I foolishly expected this to work:

SELECT q.*, q.id as q_id, t.town, c.category,
  count(qa.id) AS answers,
  count(qe.id) AS waiting
FROM qq_local q
  LEFT JOIN qa_local qa
    ON q.id = qa.q_id
      AND qa.active = 1
  LEFT JOIN qe_email qe
    ON q.id = qe.q_id
  LEFT JOIN towns t
    ON t.id = q.town_id
  LEFT JOIN q_category c
    ON q.category_id = c.id
  GROUP BY q.q
  ORDER BY q.q_id DESC

Unfortunatly, the "answers" and "waiting" numbers are always identical; because the joins cannot be counted correctly.

Does anyone know of a solution??

--BB

 

[BB101]

Anyone? This is quite urgent...

--BB

 

[BB101]

In answer to my own question, using SELECT count(DISTINCT x) worked.

--BB