Problem with full outer join doing the filter with where

Navigation
0

Friends,

I have a database with the following tables:

types 

id | nome
1  | Duplo
2  | Triplo

availability 

    data   | tipo | ocupado | manutencao | referencia
01/03/2018 | 1    | 1       | 0          | 54684
01/03/2018 | 1    | 0       | 1          | 4525
01/03/2018 | 1    | 1       | 0          | 54686

I would like to make a query that would return the following data: 

   data    | nome   | ocupado | manutencao 
01/03/2018 | Duplo  | 2       | 1
01/03/2018 | Triplo | 0       | 0

p> 
SELECT
d.data,
t.nome,
COALESCE(SUM(ocupado),0) ocupado,
COALESCE(SUM(manutencao),0) manutencao
FROM disponibilidade d
FULL OUTER JOIN tipos t ON d.tipo = t.id
WHERE d.data between '03-01-2018' AND '03-05-2018'
GROUP BY d.data, t.nome

I made this fiddle to check the structure of the database.

How to use join in a way that gives the date filter with WHERE and returns the result I need?

    
asked by anonymous 26.02.2018 / 21:01

1 answer

1

As it is being done, it will not bring any Triple name record as it is not present in the availability table because FULL OUTER JOIN is being used, I recommend performing the search by starting with the type table and using LEFT JOIN as follows: 

SELECT 
    D.DATA, 
    T.NOME,
    COALESCE(SUM(OCUPADO),0) OCUPADO,
    COALESCE(SUM(MANUTENCAO),0) MANUTENCAO
FROM 
    TIPOS T
LEFT JOIN 
    DISPONIBILIDADE D ON D.TIPO = T.ID
GROUP BY 
    D.DATA, T.NOME
    
26.02.2018 / 21:08
How to change the colors of a Jquery implementation? Why is the text label unchanged?