Show data from table even if there is no data-CodePudding

I have 3 tables:

Data in Compania table:

Data in Periodo table:

Data in VAC_PERIODOCIA table:

I want to show all the companies (COMPANIA) and the value in (vac_aplica column) searching by Periodo, whether or not they are registered.

I tried this:

SELECT  
    COMPANIA.CIA_CLAVE, COMPANIA.CIA_NOM, 
    CASE 
        WHEN VAC_PERIODOCIA.VAC_APLICA IS NULL 
            THEN 'N' 
            ELSE 'Y'  
    END VAC_APLICA
FROM
    COMPANIA 
LEFT JOIN
    VAC_PERIODOCIA ON COMPANIA.CIA_CLAVE = VAC_PERIODOCIA.CIA_CLAVE 
WHERE 
    VAC_PERIODOCIA.PERIODO = '2018 - 2019'

Result:

What I want is this:

CodePudding user response：

First of all, the question is a mess: tables and columns from the question and examples you've provided us with are different. Please fix that.

I don't speak Spanish, so I can only assume the VAC_PERIODICA is Periodo. In that case you need to move what you have in where condition to the join clause. Like this

SELECT  COMPANIA.CIA_CLAVE,COMPANIA.CIA_NOM, 
        CASE 
          WHEN Periodo.valor IS NULL THEN 'N' 
          ELSE 'Y' 
        END VAC_APLICA
FROM Compania 
LEFT JOIN Periodo 
  ON COMPANIA.CIA_CLAVE = Periodo.valor 
 AND Periodo.PERIODO = '2018 - 2019'
order by 1

dbfiddle