SQL query syntax

Navigation
0

Can anyone help to produce the SQL query syntax to get the result below?

I need all the values in the "Budgets" table to be listed, but at the same time that all the categories are listed as well (even if you do not have budgets registered for the category it should be displayed).

Budgets table 

+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| id | ano  | id_categoria | id_condominio | mes_01  | mes_02  | mes_03  | mes_04  | mes_05  | mes_06  | mes_07  | mes_08  | mes_09  | mes_10  | mes_11  | mes_12  |
+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|  1 | 2018 |            1 |            19 | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  |
|  2 | 2019 |            1 |            19 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 |
|  3 | 2018 |            7 |            19 | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 600.00  |
+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

Categories Table 

+----+-----------------------------+---------------+
| id |       nome_categoria        | id_condominio |
+----+-----------------------------+---------------+
|  1 | Cotas do Mês               | NULL          |
|  2 | Juros                       | NULL          |
|  3 | Multas                      | NULL          |
|  4 | Descontos                   | NULL          |
|  5 | Tarifa bancária            | NULL          |
|  6 | Rendimento de Poupança     | NULL          |
|  7 | Rendimento de Investimentos | NULL          |
|  8 | Multas Infrações          | NULL          |
|  9 | Gás                        | NULL          |
| 10 | Ãgua                       | NULL          |
| 11 | Energia                     | NULL          |
| 12 | Retenções                 | NULL          |
| 13 | Fundo de Reserva            | NULL          |
| 14 | Atualização Monetária    | NULL          |
| 15 | Honorário Advocatício     | NULL          |
| 16 | Pagamentos a Menor          | NULL          |
| 17 | Pagamentos a Maior          | NULL          |
| 18 | Fundo de Obras              | NULL          |
| 19 | Rateio Extra                | NULL          |
| 20 | Acordo                      | NULL          |
+----+-----------------------------+---------------+

Result 

+----+-----------------------------+---------------+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| id |       nome_categoria        | id_condominio | id | ano  | id_categoria | id_condominio | mes_01  | mes_02  | mes_03  | mes_04  | mes_05  | mes_06  | mes_07  | mes_08  | mes_09  | mes_10  | mes_11  | mes_12  |
+----+-----------------------------+---------------+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|  1 | Cotas do Mês               | NULL          |  1 | 2018 |            1 |            19 | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  | 200.95  |
|    |                             |               |  2 | 2019 |            1 |            19 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 | 5000.00 |
|  2 | Juros                       | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
|  3 | Multas                      | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
|  4 | Descontos                   | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
|  5 | Tarifa bancária            | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
|  6 | Rendimento de Poupança     | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
|  7 | Rendimento de Investimentos | NULL          |  3 | 2018 |            7 |            19 | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 250.00  | 600.00  |
|  8 | Multas Infrações          | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
|  9 | Gás                        | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 10 | Ãgua                       | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 11 | Energia                     | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 12 | Retenções                 | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 13 | Fundo de Reserva            | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 14 | Atualização Monetária    | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 15 | Honorário Advocatício     | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 16 | Pagamentos a Menor          | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 17 | Pagamentos a Maior          | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 18 | Fundo de Obras              | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 19 | Rateio Extra                | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
| 20 | Acordo                      | NULL          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
+----+-----------------------------+---------------+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
    
asked by anonymous 22.01.2018 / 02:21

1 answer

0

Just make a LEFT JOIN : 

SELECT * FROM Categorias a
LEFT JOIN Orcamentos b ON a.id = b.id_categoria
ORDER BY a.id;

If you want to list only a specific budget year, you can do so: 

SELECT * FROM Categorias a
LEFT JOIN Orcamentos b ON a.id = b.id_categoria AND b.ano = 2018
ORDER BY a.id;
    
22.01.2018 / 02:26
Why is the individual letters being printed? and not the array words like "SAMSUNG" for example? Get the value of custom attributes in .NET CORE 2.0 aspnet-identity