GroupBy clause removing all null column values

Question

I have written the following query wherein I am usig groupby clause on server column

select s.server, MAX(s.ipAddress) as ipAddress, 
        MAX(r.stacks->>"$[0].name") as stackName,
        MAX(a.aMessage) as aMessage
        from environments e
        inner join servers s
            on e.objectId  = s.environmentId
        inner join resources r
            on e.objectId  = r.environmentId     
        inner join audits a
            on a.id  = (select max(a.id) from audits a where a.logObjId = s.cAudit)  
        WHERE dateSubmitted BETWEEN NOW() - INTERVAL 90 DAY AND NOW()
        Group by s.server
        ORDER BY dateSubmitted; 

Howerver, server column may have NULL values with a valid ipAddress and stackName.

How to modify the query so that all NULL server column values are not missed out.

Expected Sample Data:

server  ipAddress   stackName   aMessage
NULL    NULL    Stack A Searching for IP pool
NULL    NULL    Stack B Message XYZ
NULL    NULL    Stack A Message ABC

Answer 1

It seems the INNER JOIN used to JOIN the table makes NULL value to removed from the result. So just modified the query. Try this one and see if you are able to see all the data of Server table so that NULL data also will come for Server column.

select s.server, MAX(s.ipAddress) as ipAddress, 
            MAX(r.stacks->>"$[0].name") as stackName,
            MAX(a.aMessage) as aMessage
            from servers s
            left join environments e
                on e.objectId  = s.environmentId
            left join resources r
                on e.objectId  = r.environmentId     
            left join audits a
                on a.id  = (select max(a.id) from audits a where a.logObjId = s.cAudit)  
            WHERE dateSubmitted BETWEEN NOW() - INTERVAL 90 DAY AND NOW()
            Group by s.server
            ORDER BY dateSubmitted;