Insert multiple items with the same Sql sever Sql

0

Hello How can I insert multiple items into a single ID Primarykey autoincrement

There will still be columns of IDproducts where I want to insert multiple items into a RequestID as I do so I've tried with update plus it will override the previous record. Thanks in advance.

    
asked by anonymous 02.11.2017 / 18:24

2 answers

1

Hello, I did it however I can not get the product name and the value follows what I did to analyze where I am wrong?

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

Displaying the order items I am unable to bring the name and values as below is displayed

How do I bring the names 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
    
04.11.2017 / 15:50
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:

  • REQUEST_ID
  • ID_ITEM

In this new table you will have the Item ID and Order ID (from your previous table). Imagining you have a table of items in the system as well. Then you will be able to make the relationship and the JOINS needed 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
    
03.11.2017 / 22:26