Make a SELECT multiple tables and save to a spreadsheet

Asked

Viewed 143 times

2

I’m trying to make a SELECT with multiple tables, where I have a product with your information, and I have another table structure to be able to add an extra field and its information.

What I tried to do was this:

SELECT p.codigo, p.compania_id, p.data_expiracao_registro, p.descricao,  p.descricao_breve, g.identificacao AS gtin, p.registro, p.altura, p.largura, p.profundidade, p.unidade_armazenamento, p.validade_meses, p.sku, ce.nome, cev.valor  INTO OUTFILE “C:/Users/coss/Desktop/produto_31-Julho.csv" FIELDS TERMINATED BY '$' LINES TERMINATED BY '\n' FROM produto AS p JOIN produto_campo_extra_valor AS pv ON (p.id = pv.produto_campos_extra_id) JOIN campo_extra AS ce ON (pv.campos_extra_idx = ce.id) JOIN campo_extra_valor AS cev ON (pv.campo_extra_valor_id = cev.id) JOIN codigogs1 AS g ON (p.gtin_id = g.id);

However it does not return the expected shows only a tuple for the product with an extra field, and I have to assemble a spreadsheet with all fields of the product.

This is my table structure:

product:

+-------------------------+--------------+------+-----+---------+--------------+
| Field                   | Type         | Null | Key | Default | Extra        |
+-------------------------+--------------+------+-----+---------+--------------+
| id                      | bigint(20)   | NO   | PRI | NULL    |auto_increment|
| version                 | bigint(20)   | NO   |     | NULL    |              |
| codigo varchar(255) | NO   | UNI | NULL    |                |
| compania_id             | bigint(20)   | NO   | MUL | NULL    |              |
| data_expiracao_registro | datetime     | YES  |     | NULL    |              |
| descricao               | varchar(255) | YES  |     | NULL    |              |
| descricao_breve         | varchar(255) | YES  |     | NULL    |              |
| gtin_id                 | bigint(20)   | NO   | MUL | NULL    |              |
| registro                | varchar(255) | YES  |     | NULL    |              |
| altura                  | float        | YES  |     | NULL    |              |
| largura                 | float        | YES  |     | NULL    |              |
| profundidade            | float        | YES  |     | NULL    |              |
| unidade_armazenamento   | int(11)      | YES  |     | NULL    |              |
| validade_meses          | int(11)      | YES  |     | NULL    |              |
| sku                     | varchar(255) | YES  |     | NULL    |              |
+-------------------------+--------------+------+-----+---------+--------------+

product:

    +-------------------------+------------+------+-----+---------+-------+
    | Field                   | Type       | Null | Key | Default | Extra |
    +-------------------------+------------+------+-----+---------+-------+
    | produto_campos_extra_id | bigint(20) | YES  |     | NULL    |       |
    | campo_extra_valor_id    | bigint(20) | YES  | MUL | NULL    |       |
    | campos_extra_idx        | int(11)    | YES  |     | NULL    |       |
    +-------------------------+------------+------+-----+---------+-------+

extra field:

+-----------+--------------+------+-----+---------+----------------+
    | Field     | Type         | Null | Key | Default | Extra          |
    +-----------+--------------+------+-----+---------+----------------+
    | id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
    | version   | bigint(20)   | NO   |     | NULL    |                |
    | descricao | varchar(255) | YES  |     | NULL    |                |
    | dominio   | varchar(255) | NO   | MUL | NULL    |                |
    | nome      | varchar(255) | NO   |     | NULL    |                |
    +-----------+--------------+------+-----+---------+----------------+

value:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id  bigint(20)   | NO   | PRI | NULL    | auto_increment |
| version bigint(20)   | NO   |     | NULL    |                |
| campo_extra_id bigint(20)   | NO   | MUL | NULL    |                |
| valor varchar(255) | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

My difficulty is being how to make appear for each product only the value of the extra field, and in the select column be the name of the extra field.

Example: Product:

                     id: 1
                version: 34
                 codigo: 9000200502
            compania_id: 1
data_expiracao_registro: NULL
              descricao: Cerasorb M 500-1000 ╡m 2X0,5cc
        descricao_breve: Cerasorb M 500-1000 ╡m 2X0,5cc
                gtin_id: 1
               registro: 10356500033
                 altura: 135
                largura: 116
           profundidade: 32
  unidade_armazenamento: 96
         validade_meses: 48
                    sku:

product:

*************************** 1. row ***************************
produto_campos_extra_id: 1
   campo_extra_valor_id: 2056
       campos_extra_idx: 1
*************************** 2. row ***************************
produto_campos_extra_id: 1
   campo_extra_valor_id: 8369
       campos_extra_idx: 2

extra field:

*************************** 1. row ***************************
       id: 1
  version: 0
descricao: Famφlia
  dominio: PRODUTO
     nome: familia
*************************** 2. row ***************************
       id: 2
  version: 0
descricao: Subfamilia
  dominio: PRODUTO
     nome: subfamilia

value:

*************************** 1. row **********
            id: 2056
       version: 0
campo_extra_id: 2
         valor: BIO
*************************** 2. row **********
            id: 8369
       version: 0
campo_extra_id: 3
         valor:

In this case I want to generate a spreadsheet with the values of the product and its respective extra fields, someone can help me with this select?

Expected result

*************************** 1. row **********
             codigo: 9000200502
    data_expiracao_registro: NULL
                  descricao: Cerasorb M 500-1000 ╡m 2X0,5cc
            descricao_breve: Cerasorb M 500-1000 ╡m 2X0,5cc
                    gtin: 111111111
                   registro: 10356500033
                     altura: 135
                    largura: 116
               profundidade: 32
      unidade_armazenamento: 96
             validade_meses: 48
                        sku:
                     nome: familia
                       valor: BIO
*************************** 2. row **********
       codigo: 9000200502
    data_expiracao_registro: NULL
                  descricao: Cerasorb M 500-1000 ╡m 2X0,5cc
            descricao_breve: Cerasorb M 500-1000 ╡m 2X0,5cc
                    gtin: 111111111
                   registro: 10356500033
                     altura: 135
                    largura: 116
               profundidade: 32
      unidade_armazenamento: 96
             validade_meses: 48
                        sku:
                     nome: subfamilia
                       valor:

I know I’m doing something wrong because only the first line appears, and in this example is two because I have two extra fields related to this product, but the real is that I have a varied size of extra fields.

EDIT

I tried to make a trial to mount in another table my output but this giving errors:

CREATE TABLE saida(
  tupla varchar(500)
);

DELIMITER |

DROP PROCEDURE IF EXISTS exportar_produtos |
CREATE PROCEDURE exportar_produtos()
BEGIN
    DROP TABLE IF EXISTS produtos_exportados;
    CREATE TEMPORARY TABLE produtos_exportados (
      id                        bigint(20),
      codigo                    varchar(255),
      data_expiracao_registro   datetime,
      descricao                 varchar(255),
      descricao_breve           varchar(255),
      gtin                      varchar(255),
      registro                  varchar(255),
      altura                    float,
      largura                   float,
      profundidade              float,
      unidade_armazenamento     int(11),
      validade_meses            int(11),
      sku                       varchar(255)
    );

    INSERT INTO produtos_exportados (id, codigo, data_expiracao_registro, descricao, descricao_breve, gtin, registro, altura, largura, profundidade,unidade_armazenamento, validade_meses, sku)
      SELECT p.id, p.codigo, p.compania_id, p.data_expiracao_registro, p.descricao,  p.descricao_breve, g.identificacao, p.registro, p.altura, p.largura, p.profundidade, p.unidade_armazenamento, p.validade_meses, p.sku
        FROM produto AS p JOIN codigogs1 AS g ON (p.gtin_id = g.id);

      CALL gerar_saida();
END |

DROP PROCEDURE IF EXISTS gerar_saida |
CREATE PROCEDURE gerar_saida()
BEGIN
  DECLARE id_produto bigint(20);
  DECLARE t_campos_extras VARCHAR(500);
  DECLARE v_finished INTEGER DEFAULT 0;

  DEClARE lista CURSOR FOR
    SELECT id FROM produtos_exportados;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

  /*DELETE FROM debug;

  DECLARE CONTINUE HANDLER FOR 1172
  BEGIN
    INSERT INTO debug(msg) VALUES (CONCAT("Problema no produto: ", id_produto, ". "));
  END;*/

  OPEN lista;
  interno_get: LOOP
    FETCH lista INTO id_produto;
    IF v_finished = 1 THEN
      LEAVE interno_get;
    END IF;

    CALL buscar_campo_extra_produto(id_produto, t_campos_extras);

    INSERT INTO saida(tupla) VALUES (
      SELECT CONCAT(pe.codigo, "$", pe.compania_id, "$", pe.data_expiracao_registro, "$", pe.descricao, "$",  pe.descricao_breve, "$", pe.gtin, "$", pe.registro, "$", pe.altura, "$", pe.largura,
        "$", pe.profundidade, "$", pe.unidade_armazenamento, "$", pe.validade_meses, "$", pe.sku, "$", t_campos_extras) FROM produtos_exportados AS pe WHERE pe.id = id_produto;

  END LOOP interno_get;
  CLOSE lista;
END |

DROP PROCEDURE IF EXISTS buscar_campo_extra_produto |
CREATE PROCEDURE buscar_campo_extra_produto(IN id_produto INT, OUT t_campos_extras VARCHAR)
BEGIN
  DECLARE t_nome varchar(255);
  DECLARE t_valor varchar(255);
  DECLARE v_finished INTEGER DEFAULT 0;

  DEClARE campos_extras CURSOR FOR
    SELECT ce.nome, cev.valor
      FROM produto_campo_extra_valor AS pv
        JOIN campo_extra AS ce ON (pv.campos_extra_idx = ce.id)
        JOIN campo_extra_valor AS cev ON (pv.campo_extra_valor_id = cev.id)
      WHERE pv.produto_campos_extra_id = id_produto;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

  DELETE FROM debug;

  DECLARE CONTINUE HANDLER FOR 1172
  BEGIN
    INSERT INTO debug(msg) VALUES (CONCAT("Problema no produto: ", id_produto, ". "));
  END;

  SET t_campos_extras = "";
  OPEN campos_extras;
  interno_get: LOOP
    FETCH campos_extras INTO t_nome, t_valor;
    IF v_finished = 1 THEN
      LEAVE interno_get;
    END IF;

    SET t_campos_extras = CONCAT(t_campos_extras, "$", t_nome, "$", t_valor);

  END LOOP interno_get;
  CLOSE campos_extras;
END |

DELIMITER;

Follow the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near

This error appears in two points in the CONCAT(pe.codigo, "$", pe.com... and in the 

)
    BEGIN
      DECLARE t_nome varchar(255);

This I don’t even know if this trial will be helping me to assemble the result I hope.

mysql sql join procedure
  • 3

    Marcius, is it necessary to inform the "p." for each field of the product table? I ask the question as a suggestion. And if possible, post the message that is returned with the error, because code 1064 is an sql syntax error and usually it points out where it was not possible to interpret the command.

  • So Rodrigo really needs yes, at the time of transcribing I forgot, but still does not do the expected

  • Marcius, can you pass the error that the bank is returning? To know exactly where it is pointing the fault.

  • Not returning error, just does not do what I expect.

  • 1

    You can put in your question the expected result?

  • You can ask a Rigger, you have a question corresponding to it at the following link: https://dba.stackexchange.com/questions/39474/find-and-insert-row-to-another-table-using-mysql-trigger

  • So you can’t be a Rigger because I already have the data on the table and I just want to collect it, I thought about a trial, but I’m stuck on how to get all the information and play for a time table.

Show 2 more comments

1 answer

-1

I don’t know if it’ll help you, but I had a similar problem, what I did was migrate the gang to firebird, created only the tables I needed and entered the data of the other gang, I used the ibexpert 2015 and then with the help of the staff here I managed to solve very easy, here’s what I used.

    SELECT VENCIMENTO,CTARECEBER.ID,CTARECEBER.NUMERO,CTARECEBER.emissao,DATAPAGAMENTO,VALOR,VALORPAGO,clientes.NOME
          FROM CTARECEBER
                JOIN clientes 
                ON ctareceber.matricula=clientes.matricula;

Seems to me in the ibexpert to be much easier to do select and join.

  • 2

    Suggesting the user to migrate DB technology just to use a primitive graphical tool just to be able to do a JOIN definitely does not seem like a good suggestion. Remembering that you can [Dit] your post at any time explaining better what the tool did to solve the problem, then maybe value the answer.

Browser other questions tagged mysql sql join procedure

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