Select more than one column and remove NULL values from result-CodePudding

I'm trying to create a query in MySQL, but I'm having difficulties. I can already select one column, but would like to select more than one. This is the code I'm using:

    SELECT
      e.IDCARTEIRA,
      (SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202109') AS 'Ref 09 2021',
      (SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202110') AS 'Ref 10 2021'
      FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` e
      GROUP BY e.IDCARTEIRA;

This is the query result:

IDCARTEIRA | Ref 09 2021 | Ref 10 2021
       1   |    39,76    |    (null)
       2   |   (null)    |    174,97
       3   |   (null)    |    (null)
       4   |   388,33    |    (null)
       5   |   (null)    |    (null)

But I would like to select more columns, like:

    SELECT
      e.IDCARTEIRA, e.otherColumn, e.AnotherColumn [...]

In addition, I would also like to eliminate null results.

This would be the expected result (only show rows with non empty Ref 09 2021 and non empty Ref 10 2021 columns)

IDCARTEIRA | OtherColumn |Ref 09 2021 | Ref 10 2021
       1   |     Value   |    39,76   |    N/A  
       2   |     Value   |     N/A    |   174,97
       4   |     Value   |    388,33  |    N/A

Does anyone know how I can do these two things?

I tried:

    SELECT
       e.IDCARTEIRA,
       e.MATRICULA,
       e.LIG_AGUA_SIT,
      COALESCE((SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202109' AND `CONSUMO_FATURADO_MES` - `CONS_MEDIO_AG_6_MESES` > 0),'') AS 'Referencia 09 2021',
      COALESCE((SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202110' AND `CONSUMO_FATURADO_MES` - `CONS_MEDIO_AG_6_MESES` > 0),'') AS 'Referencia 10 2021'
      
      FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` e
      GROUP BY e.VALORAGUA ORDER BY e.IDCARTEIRA;

But the row 4 not showing:

4 | 121112 | CORTADO  |  388,33

@FanoFN suggestion:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e6e691dc4c8b4edc17e2446049497123

In the case of the example shown in the link I need only lines 1, 2 and 4 to be displayed. In other words, I need only the non-empty rows of the columns "Ref 09 2021" and "Ref 10 2021" to be displayed.

CodePudding user response：

If you're just looking for a quick solution on your current attempt then might I suggest doing something like this:

 SELECT
      e.IDCARTEIRA,
      COALESCE((SELECT `VALORAGUA` FROM VW_CADASTRO_HIDROMETROPOLITANO WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202109'),'') AS 'Ref 09 2021',
      COALESCE((SELECT `VALORAGUA` FROM VW_CADASTRO_HIDROMETROPOLITANO WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202110'),'') AS 'Ref 10 2021'
      FROM VW_CADASTRO_HIDROMETROPOLITANO e
      GROUP BY e.IDCARTEIRA
HAVING CASE WHEN `Ref 09 2021`='' AND `Ref 10 2021`='' THEN 0 ELSE 1 END=1;

Add HAVING with CASE statement on your custom generated columns. Here's a slightly shorter alternative query:

SELECT IDCARTEIRA,
       MAX(CASE WHEN `REFERENCIA_MES_ANO` = '202109' THEN `VALORAGUA` ELSE '' END) AS 'Ref 09 2021',
       MAX(CASE WHEN `REFERENCIA_MES_ANO` = '202110' THEN `VALORAGUA` ELSE '' END) AS 'Ref 10 2021' 
FROM VW_CADASTRO_HIDROMETROPOLITANO
GROUP BY IDCARTEIRA
HAVING CASE WHEN `Ref 09 2021`='' AND `Ref 10 2021`='' THEN 0 ELSE 1 END=1;

Demo fiddle