gistfile1.sql

SELECT root_node.id, lookup_progresses.children_total, GROUP_CONCAT(children.id) as child_ids
FROM contents as root_node
LEFT JOIN lookup_progresses ON root_node.id = lookup_progresses.content_id
LEFT JOIN contents as children on root_node.id = children.parent_id
WHERE root_node.content_type_id = 2 AND (SELECT count(id) FROM contents WHERE parent_id = root_node.id AND content_type_id = 6) > 0
GROUP BY root_node.id;