CODEWITHSUNDEEP
sqlselectjoincount
Francesco
Francesco

Reputation: 964

SQL query COUNT return zero with multiple tables JOIN

This is the scenario: There are 4 tables - CF {ID, NAME} - CLASS {ID, CLASSCODE, CF_ID} - OWNER {ID, AREACODE, CF_ID} - REGION {ID, AREACODE, REGIONCODE}

CF_ID is a foreign key pointing at CF.ID

I would like to query the database in order to obtain 3 columns: - REGIONCODE - CLASSCODE - FREQUENCY (the number of occurrences of CF.ID), GROUP BY REGIONCODE and CLASSCODE

The tricky part is that the dataset returned by the query should include rows where the frequency is 0 in case no occurrences of a specific CLASSCODE are found in a specific REGIONCODE. Basically I would like to have the carthesian product of REGIONCODE (DISTINCT) per CLASSCODE (DISTINCT) enumerating all the occurrences even if the FREQUENCY is zero.

This is the query I wrote:

SELECT 
REGION.REGIONCODE, CLASS.CLASSCODE, COUNT(CF.ID) AS Frequency 
FROM REGION 
JOIN OWNER ON REGION.AREACODE = OWNER.AREACODE
JOIN CF ON OWNER.CF_ID = CF.ID 
JOIN CLASS ON CLASS.CF_ID = CF.ID
GROUP BY CLASS.CLASSCODE, REGION.REGIONCODE
ORDER BY REGION.REGIONCODE, CLASS.CLASSCODE

Upvotes: 1

Views: 878

Answers (1)

Laurence
Laurence

Reputation: 10976

Use a cross join to get the cartesian product, and then left outer joins to ensure you don't lose any.

Select
    r.RegionCode,
    c.ClassCode,
    Count(cf.Id) AS Frequency 
From
    Region r
        Cross Join
    Class c
        Left Outer Join
    Owner o
        On r.AreaCode = o.AreaCode
        Left Outer Join
    CF
        On o.Cf_Id = cf.Id And
           c.Cf_Id = cf.Id
Group By
    c.ClassCode,
    r.RegionCode
Order By
    r.RegionCode,
    c.ClassCode

Upvotes: 1

Related Questions