Syntax SQL query

Asked

Viewed 53 times

0

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

I need all values of the "Budgets" table to be listed, but at the same time all categories should be listed as well (even if there is no budget registered for the category it should be displayed).

Table Budgets

+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| 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  |
+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

Table Categories

+----+-----------------------------+---------------+
| 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          |
+----+-----------------------------+---------------+

Upshot

+----+-----------------------------+---------------+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| 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          |    |      |              |               |         |         |         |         |         |         |         |         |         |         |         |         |
+----+-----------------------------+---------------+----+------+--------------+---------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
mysql database

1 answer

0


All you have to do is 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 one specific year of the budget, 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;

  • Thanks! But I enter a WHERE to list a specific year of a condo does not return all categories. Ex.: SELECT * FROM financeiro_categorias_receitas a LEFT JOIN financei_orcamentos_receitas b ON a.id = b.id_categoria WHERE b.id_condominio = 19 AND b.ano = 2018 ORDER BY a.id;

  • put in the ON, I’ll change the answer

  • Muto thanks! All right now.

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.