List data from 2 tables linked to the parent table

Question

ORDER TABLE

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

TABLE PEDIDO_ITENS

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

SCHEDULE PAYMENTS

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

Now I need to make a consultation that presents me as follows:

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

I gave it a try but it didn’t 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');

Answer 1

Your query is correct, just missed include the group by in the first query. Follow 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 tables the auto-increment columns are being included in the Inserts.

Whereas the blank 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