news-hierarchy.sql

news-hierarchy.sql

SELECT 
    pp.parent_pid,
    COUNT(pp.child_pid) AS 'episode_count',
    versions.version_count,
    broadcasts.broadcast_count
FROM
    pip_pip pp
        JOIN
    (SELECT 
        pp.parent_pid AS 'brand_pid',
            COUNT(v.pid) AS 'version_count'
    FROM
        pip_pip pp
    JOIN pip_pip ppv ON ppv.parent_pid = pp.child_pid
    JOIN version v ON v.pid = ppv.child_pid
    WHERE
        pp.parent_pid = 'p002vsmz') AS versions ON versions.brand_pid = pp.parent_pid
        JOIN
    (SELECT 
        pp.parent_pid AS 'brand_pid',
            COUNT(b.pid) AS 'broadcast_count'
    FROM
        pip_pip pp
    JOIN pip_pip ppv ON ppv.parent_pid = pp.child_pid
    JOIN broadcast b ON b.version_pid = ppv.child_pid
    WHERE
        pp.parent_pid = 'p002vsmz') AS broadcasts ON broadcasts.brand_pid = pp.parent_pid
WHERE
    pp.parent_pid = 'p002vsmz'
GROUP BY pp.parent_pid