List data from 2 tables linked to parent table

Question

List data from 2 tables linked to parent table

Navigation

#1 by (1 votes)

0

ORDERS TABLE

---------------------------
| id_pedido | pedido_data |
---------------------------
|    1      | 2016-01-01  |
---------------------------

TITLE ORDER_ITENS

-------------------------------------
| id_item | id_pedido | valor_total |
-------------------------------------
|    1    |     1     |    10.00    |
-------------------------------------
|    2    |     1     |     5.00    |
-------------------------------------

PAYMENTS TABLE

-----------------------------------------------
| id_pgt | id_pedido | data_pgto | valor_pago |
-----------------------------------------------
|   1    |     1     | 2016-01-10|    2.00    |
-----------------------------------------------

I now need to make a query that looks like this:

-------------------------------------------------
| id_pedido |    data    |  Débito  |  Crédito  |
-------------------------------------------------
|     1     | 2016-01-01 |   15.00  |           |
-------------------------------------------------
|     1     | 2016-01-10 |          |    2.00   |
-------------------------------------------------

I tried, but it did not work:

(SELECT p.id_pedido, p.pedido_data, SUM(pi.valor_total) AS vTotal FROM pedidos AS p
    INNER JOIN pedido_itens AS pi ON pi.id_pedido=p.id_pedido)
UNION ALL
(SELECT p.id_pedido, pg.data_pgto, pg.valor_pago AS vTotalPago FROM pedidos AS p
    INNER JOIN pagamentos AS pg ON pg.id_pedido=p.id_pedido)

Below SQL of the tables:

   --
   -- Estrutura da tabela 'pagamentos'
   --

   CREATE TABLE IF NOT EXISTS 'pagamentos' (
   'id_pgto' int(11) NOT NULL AUTO_INCREMENT,
   'id_pedido' int(11) NOT NULL,
   'forma_pgto' enum('1','2','3','4') DEFAULT NULL COMMENT '1=Dinheiro    2=Cheque 3=Cartao 4=Boleto',
   'data_pgto' date NOT NULL DEFAULT '0000-00-00',
   'valor_pago' decimal(10,2) NOT NULL,
   PRIMARY KEY ('id_pgto'),
   KEY 'id_pedido' ('id_pedido')
   ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

   --
   -- Extraindo dados da tabela 'pagamentos'
   --

   INSERT INTO 'pagamentos' ('id_pgto', 'id_pedido', 'forma_pgto', 'data_pgto', 'valor_pago') VALUES
   (1, 36, '1', '2016-09-15', '10.00');

   -- --------------------------------------------------------

   --
   -- Estrutura da tabela 'pedidos'
   --

   CREATE TABLE IF NOT EXISTS 'pedidos' (
     'id_pedido' int(11) NOT NULL AUTO_INCREMENT,
     'pedido_data' date NOT NULL DEFAULT '0000-00-00',
     'pedido_cliente' int(11) NOT NULL,
     'pedido_status' enum('1','2','3') NOT NULL DEFAULT '1',
     PRIMARY KEY ('id_pedido'),
     KEY 'id_cliente' ('pedido_cliente')
   ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

   --
   -- Extraindo dados da tabela 'pedidos'
   --

   INSERT INTO 'pedidos' ('id_pedido', 'pedido_data', 'pedido_cliente','pedido_status') VALUES
   (36, '2016-09-13', 6, '1');

   -- --------------------------------------------------------

   --
   -- Estrutura da tabela 'pedido_itens'
   --

   CREATE TABLE IF NOT EXISTS 'pedido_itens' (
     'id_item' int(11) NOT NULL AUTO_INCREMENT,
     'id_pedido' int(11) NOT NULL,
     'id_produto' int(11) NOT NULL,
     'qtde' int(11) NOT NULL,
     'valor_unit' decimal(10,2) NOT NULL,
     'valor_total' decimal(10,2) NOT NULL,
     PRIMARY KEY ('id_item'),
     KEY 'id_pedido' ('id_pedido')
   ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=46 ;

   --
   -- Extraindo dados da tabela 'pedido_itens'
   --

   INSERT INTO 'pedido_itens' ('id_item', 'id_pedido', 'id_produto', 'qtde','valor_unit', 'valor_total') VALUES
   (44, 36, 5, 2, '1.00', '10.00'),
   (45, 36, 5, 2, '1.00', '5.00');

sql join select

asked by anonymous 16.09.2016 / 03:53

1 answer

How to put image in the background of Wordpress Upload data from a query to a lightbox

score 1 · Accepted Answer

Your query is correct, you just needed to include group by the first query. Here's the query:

SELECT p.id_pedido, p.pedido_data, SUM(pi.valor_total) AS vTotal 
FROM pedidos AS p INNER JOIN 
     pedido_itens AS pi ON pi.id_pedido=p.id_pedido
group by p.id_pedido, p.pedido_data
UNION ALL
SELECT p.id_pedido, pg.data_pgto, pg.valor_pago AS vTotalPago 
FROM pedidos AS p INNER JOIN 
     pagamentos AS pg ON pg.id_pedido=p.id_pedido

I just found it strange that in the creation of the tables the auto-increment columns are being included in the inserts.

Since the white columns will always be zero, you can do the following:

SELECT p.id_pedido, p.pedido_data, SUM(pi.valor_total) AS Debito, (p.id_pedido-p.id_pedido) as Credito 
FROM pedidos AS p INNER JOIN 
     pedido_itens AS pi ON pi.id_pedido=p.id_pedido 
group by p.id_pedido, p.pedido_data,  (p.id_pedido-p.id_pedido)
UNION ALL
SELECT p.id_pedido, pg.data_pgto, 0 as Debito, pg.valor_pago AS Credito 
FROM pedidos AS p INNER JOIN 
     pagamentos AS pg ON pg.id_pedido=p.id_pedido 


id_pedido   pedido_data Debito  Credito
1           2016-09-13   15.00     0.00
1           2016-09-15    0.00    10.00