SELECT DISTINCT(n.nid), n.title, n.changed, COUNT(p.DISTINCT(n.nid)) AS count FROM node n LEFT JOIN project_issues p ON n.nid = p.pid AND p.state
But it seems that the result should really be the following? (difference in the COUNT DISTINCT):
Yes, it should be. It seems module authors made scenarios I did never dreamed of. I presumed that if someone has a field which stores a node id, it is called nid. I do not know project.module, but from this query I suspect that pid stores a node id ('cos the join using it so). On the other hand, we have something else which IS called nid. This won't work. Really won't. Look at node_db_rewrite_sql it checks for primary field being called nid. If someone would enlighten me what is stored in 'pid' and 'nid' , I would happy to assist in patching project.module Regards Karoly Negyesi