Insert several Items with the same IDPEDIDO Sql Sever

Asked

Viewed 974 times

0

Hello How can I enter multiple items for a single Primarykey autoincrement ID inserir a descrição da imagem aqui

of there will still be columns of the Idproducts where I want to insert several items for an Id_request as I do this already tried with update but it will be overwriting the previous record. Thanks in advance.

database sql-server sql-insert

  • Do you want to create another table to insert the items? This?

  • Hello then the table wanted to know if I can use this or need to use another to be able to add the items with the order

  • Yes, in this case for the modeling to be correct it is necessary to create a table Pedido_Item or something similar with foreign key for the Pedido.

  • Beauty I’ll try to do

2 answers

1


Hello then I did but I’m not getting the name of the product and the value follows what I accomplished to analyze where I’m missing?

insert into ITEMS(ID_PEDIDO, ID_PRODUTO, QUANTIDADE)values(3, 7, 1)

SELECT
    A.NOM_PRODUTO,
    A.VAL_PRODUTO,
    B.QUANTIDADE,
    B.ID_PEDIDO AS 'ITEM ID'
    FROM TB_PRODUTOS A RIGHT JOIN ITEMS B ON
        A.ID_PRODUTO = B.ID_PEDIDO WHERE B.ID_PEDIDO=3  --seleciona o pedido 3

When displaying the order items I am unable to bring the name and values as shown below inserir a descrição da imagem aqui

How I carry the name and values will be?

Solved like this:

SELECT<br>
    C.ID_PEDIDO AS 'PEDIDO',
    --A.ID_PRODUTO,
    A.NOM_PRODUTO AS'PRODUTO',
    A.VAL_PRODUTO AS 'VALOR UNITARIO',
    B.QUANTIDADE,
    (A.VAL_PRODUTO * B.QUANTIDADE)AS 'VALOR TOTAL POR ITEM'
    --B.ID_PRODUTO,

    FROM TB_PRODUTOS A INNER JOIN ITEMS B ON
        A.ID_PRODUTO = B.ID_PRODUTO
        INNER JOIN TB_PEDIDOS C ON C.ID_PEDIDO = B.ID_PEDIDO
        WHERE C.ID_PEDIDO=3
            GROUP BY C.ID_PEDIDO,
            A.NOM_PRODUTO,
            A.VAL_PRODUTO,
            B.VAL_TOTAL ,
            B.QUANTIDADE

1

You will have to change the structure of your data. Because now you want to have multiple items with the same number of Orders. So by imagining that your table is of items, you create a new table that contains the columns:

  • ID_PEDIO
  • ID_ITEM

In this new table you will have the Item ID and Order ID (from your previous table). Assuming you have a table of items in the system as well. Then you can make the relationship and JOINS necessary in your queries.

Pedido 1 - Item 1 
Pedido 1 - Item 2 
Pedido 1 - Item 3 
Pedido 1 - Item 4 
Pedido 2 - Item 1 
Pedido 2 - Item 2
  • 1

    I think I understand I’m going to do it here and send you to see if it’s right if you can’t

Browser other questions tagged database sql-server sql-insert

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