Home I'm trying to show null values as zeros, how would I join these tables together?

Questions

I'm trying to show null values as zeros, how would I join these tables together?

March 30, 2023

This is the query I have, as far as I can tell it should be correct except I am unsure how to combine the tables using the building as a common field.

SELECT l.building AS "BUILD", NVL(NUMSECTIONS, 0) AS "NUMSECTIONS"
FROM location l
LEFT OUTER JOIN 
    (SELECT building AS "BUILD", COUNT(*) AS "NUMSECTIONS"
    FROM location l
    INNER JOIN sections e
    ON l.locationid = e.locationid
    INNER JOIN courses c
    ON e.courseid = c.courseid
    WHERE subjectcode IN ('CS', 'NET', 'WEB', 'MATH', 'ENGL', 'HIST', 'ZOOL', 'ART', 'COMM', 'BSAD')
    GROUP BY building)s
ON l.building = s.building

Assigning the building in the inner table as s.building doesn’t seem to work like I need it to

>Solution :

If your query is throwing an error of s.building does not exist it is certainly because in your subquery the building column is aliased as ‘BUILD’ so you should join on l.building and s."BUILD".