I have a (local) MySQL database, which is a Drupal 6 export. It has these relevant tables:
node- Contains articles, plus also information about their department and sections. I'm interested in
content_field_department- Has the relationships between article nodes and department nodes. I'm interested in
nid(the article node) and
field_department_nid(the department node) columns.
content_field_section- Has the relationships between article nodes and section nodes. I'm interested in
nid(the article node) and
field_section_nid(the section node) columns.
I'm trying to get an accurate COUNT for how many times a given article node is related to each section or department node (performance is not relevant for me at this time, since it's local and it can take as long as it needs to without causing problems). That is, I'd like data like this:
+-----------------+-------------------+ | Section Name | Count of Articles | +-----------------+-------------------+ | Department Name | Count of Articles | +-----------------+-------------------+
One problem is when the interface was originally built, a department choice for an article also included all of the sections. A section choice, though, never included the departments. So in cases where an article node is related to another node twice, both as a section and as a department, I would like to COUNT that only once.
My current try
My current attempt is like this:
To get a department COUNT:
SELECT DISTINCT d.field_department_nid as tid, n.title as name, ( SELECT COUNT(DISTINCT nid, field_department_nid) FROM content_field_department d2 WHERE d2.field_department_nid = d.field_department_nid AND nid NOT IN ( SELECT s.nid from content_field_section s LEFT OUTER JOIN node n2 ON s.nid = n2.nid WHERE field_section_nid IS NOT NULL ) ) as drupal_department_count FROM content_field_department d LEFT OUTER JOIN node n ON d.field_department_nid = n.nid ORDER BY drupal_department_count DESC
To get a section COUNT:
SELECT DISTINCT s.field_section_nid as tid, n.title as name, ( SELECT COUNT(DISTINCT nid, field_section_nid) FROM content_field_section s2 WHERE s2.field_section_nid = s.field_section_nid AND nid NOT IN ( SELECT d.nid from content_field_department d LEFT OUTER JOIN node n2 ON d.nid = n2.nid WHERE field_department_nid IS NOT NULL ) ) as drupal_section_count FROM content_field_section s LEFT OUTER JOIN node n ON s.field_section_nid = n.nid ORDER BY drupal_section_count DESC
I had also tried without the
NOT IN additions, but the COUNT is too high, I think because the same node combination can be COUNTed in both tables.
What direction do I need to go with this?