Problem joining tables in Mysql

Asked

Viewed 51 times

0

I have 3 tables:

  • tb_carne_lancamento AS A
  • tb_partisa_x_recipe AS B
  • tb_plano_accounts AS C

In table A and B I have the id of table tb_plano_accounts and both have a value field.

I need to create a view that joins these two tables.

I’m in the fight here, but the values are repeating.

That’s the result I get:

No momento estou com esse resultado.

That’s the result I’d like:

Modelo de que preciso

Code (Schema SQL):

CREATE TABLE tb_plano_contas (
  id_plano_contas INT,
  desc_plano_contas VARCHAR (100)
);
INSERT INTO tb_plano_contas (id_plano_contas, desc_plano_contas) VALUES (1, 'Doação Via Carnê');
CREATE TABLE tb_despesa_x_receita (
  id_despesa_x_receita INT,
  data_mov DATE,
  fk_id_plano_contas INT,
  valor_desp_x_rec DECIMAL(9,2)
);
INSERT INTO tb_despesa_x_receita (id_despesa_x_receita, data_mov, fk_id_plano_contas, valor_desp_x_rec) VALUES (2173,'2015-01-30',1,1826);
INSERT INTO tb_despesa_x_receita (id_despesa_x_receita, data_mov, fk_id_plano_contas, valor_desp_x_rec) VALUES (2174,'2015-02-28',1,1532);
INSERT INTO tb_despesa_x_receita (id_despesa_x_receita, data_mov, fk_id_plano_contas, valor_desp_x_rec) VALUES (2175,'2015-03-15',1,1201);
CREATE TABLE tb_carne_lancamento (
  id_carne_lancamento INT,
  data_carne_lancamento DATE,
  id_plano_contas INT,
  valor_carne_lancamento DECIMAL(9,2)
);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (43,'2015-05-18',1,10);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (44,'2015-05-18',1,20);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (45,'2015-05-18',1,10);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (46,'2015-05-14',1,20);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (47,'2015-05-18',1,5);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (48,'2015-05-18',1,20);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (49,'2015-05-18',1,5);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (50,'2015-05-19',1,40);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (51,'2015-02-05',1,20);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (54,'2015-05-22',1,8888);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (55,'2015-01-06',1,30);
INSERT INTO tb_carne_lancamento (id_carne_lancamento, data_carne_lancamento, id_plano_contas, valor_carne_lancamento) VALUES (56,'2015-02-02',1,30);

Code (Query SQL):

SELECT * FROM tb_plano_contas;
SELECT * FROM tb_despesa_x_receita;
SELECT * FROM tb_carne_lancamento;
mysql join inner-join

  • Do you want to join (UNION) or join (JOIN)? I think you want a JOIN. Study how the junction works which may explain what you say is a "repetition". For a union the tables need to be compatible union, which does not seem to be the case of their tables, and for the case of junction the tables need to have fields with values in common. As you have not posted the query that provides the above result can’t help you any more than this.

  • SELECT a. desc_plano_accounts, b. data_mov, b. valor_desp_x_rec, b. id_despesa_x_receita,
c.data_carne_lancamento,
c.id_carne_lancamento,
c.valor_carne_lancamento
FROM
tb_plano_contas AS a
INNER JOIN tb_despesa_x_receita AS b ON b.fk_id_plano_contas = a.id_plano_contas
INNER JOIN tb_carne_lancamento AS c.id_plano_contas = a.id_plano_contas WHERE a. id_plano_accounts = 1

  • In case it would be a Join.

  • In the first table what do you consider to be a "repetition"? In the second table how can there be lines with NULL if you are doing an INNER JOIN? What is the sum total? Edit your question and add all relevant information.

  • In the image that appears the null value is excel.

  • It’s a representation I need.

  • If you look at the link where is a fragment of my database you will see the data that composes the table. https://www.db-fiddle.com/f/v3qPgvijhD1tF7GScL6ix1/2#&togetherjs=pDaJcfcq1B

  • By "null is excel value" do you mean that such data does not exist in the tables of your database? The query you posted in the comments would not produce the result displayed.

  • Exactly when the data does not exist in the table has to be null according to the second image. The first image represents the data of my database. Note that the id_redundsa_x_recipe repeats several times.

  • As what you show as a result of your query does not correspond to the query and neither are displayed the fields used as merge crité it is very difficult to help you. I end my participation here.

  • The result of the query is just image 1.

  • I couldn’t get a glimpse of any repeat line in his first image.

  • Just look in the id_redundsa_x_recipe column you will see that id repeats.

  • The value of this column effectively repeats but is associated with different values of the column id_carne_lancamento. I think you need to study what it means to join in a database.

Show 9 more comments
No answers

Browser other questions tagged mysql join inner-join

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