Consult two tables without foreign key and relationship

Question

I have two tables tb_pedido and tb_pagamento, put an example below with some dummy data. I need a report that shows the data of tb_pedido and of tb_pagamento, all in a single table as in example 3. I thought about making a left Join but could not, I just need to add the column data_pagamento and valor_pagamento to finalise the report.

TB_PEDIDO
COD_EMPRESA |COD_FORNECEDOR|DATA_EMISSAO|VALOR_PEDIDO|
1           |1             |01/11/2018  |1000        |
2           |2             |02/11/2018  |2000        |

TB_PAGAMENTO
COD_EMPRESA |COD_FORNECEDOR|DATA_ENTRADA|DATA_PAGAMENTO|VALOR_PAGAMENTO|
1           |1             |26/11/2018  |27/11/2018    |1000           |
2           |2             |26/11/2018  |28/11/2018    |2000           |


----------------------TB_PEDIDO----------------------|--------TB_PAGAMENTO----------|
COD_EMPRESA |COD_FORNECEDOR|DATA_EMISSAO|VALOR_PEDIDO|DATA_PAGAMENTO|VALOR_PAGAMENTO|
1           |1             |01/11/2018  |1000        |27/11/2018    |1000           |
2           |2             |02/11/2018  |2000        |28/11/2018    |2000           |

Answer 1

First I created the two tables and entered the data as posted in the question (Remember to post in the next questions, it makes it much easier for those who help you):

CREATE TABLE `tb_pedido` (
    `COD_EMPRESA` int(11) DEFAULT NULL,
    `COD_FORNECEDOR` int(11) DEFAULT NULL,
    `DATA_EMISSAO` timestamp DEFAULT CURRENT_TIMESTAMP,
    `VALOR_PEDIDO` decimal(15,3)
) ENGINE=InnoDb;

INSERT INTO `tb_pedido` VALUES
    (1, 1, '2018-11-01', 1000),
    (2, 2, '2018-11-02', 2000);

CREATE TABLE `tb_pagamento`(
    `COD_EMPRESA` int(11) DEFAULT NULL,
    `COD_FORNECEDOR` int(11) DEFAULT NULL,
    `DATA_ENTRADA` timestamp DEFAULT CURRENT_TIMESTAMP,
    `DATA_PAGAMENTO` timestamp DEFAULT CURRENT_TIMESTAMP,
    `VALOR_PAGAMENTO` decimal(15,3)
) ENGINE=InnoDb;

INSERT INTO `tb_pagamento` VALUES
    (1, 1, '2018-11-26', '2018-11-27', 1000),
    (2, 2, '2018-11-26', '2018-11-28', 2000);

I need a report that shows the data of tb_request and tb_payment, all in a single table as in example 3

Solution:

SELECT
    tped.`COD_EMPRESA`, tped.`COD_FORNECEDOR`, tped.`DATA_EMISSAO`, tped.`VALOR_PEDIDO`, tpag.`DATA_PAGAMENTO`, tpag.`VALOR_PAGAMENTO`
FROM `tb_pedido` AS `tped`
JOIN `tb_pagamento` AS `tpag`
    ON(tped.`COD_EMPRESA` = tpag.`COD_EMPRESA` AND tped.`COD_FORNECEDOR` = tpag.`COD_FORNECEDOR`);

Exit (As in example 3):

+-------------+----------------+---------------------+--------------+---------------------+-----------------+
| COD_EMPRESA | COD_FORNECEDOR | DATA_EMISSAO        | VALOR_PEDIDO | DATA_PAGAMENTO      | VALOR_PAGAMENTO |
+-------------+----------------+---------------------+--------------+---------------------+-----------------+
|           1 |              1 | 2018-11-01 00:00:00 |     1000.000 | 2018-11-27 00:00:00 |        1000.000 |
|           2 |              2 | 2018-11-02 00:00:00 |     2000.000 | 2018-11-28 00:00:00 |        2000.000 |
+-------------+----------------+---------------------+--------------+---------------------+-----------------+
2 rows in set (0.00 sec)

This is a simple query and should be implemented according to your application.

Remembering: Foreign keys ensure the integrity of stored data (one of the pillars of Mysql). Using this way, one can delete data from a table that would be associated with another table... Still not being a good practice, it is not the objective of the question "correct" your database, but rather reproduce a query associating the tables independent of the existence of foreign keys.

Watch this playlist and understand everything: Video Course - Mysql Databases

Answer 2

you can use the first 2 fields as foreign:

SELECT 
    a.COD_EMPRESA, a.COD_FORNECEDOR, a.DATA_EMISSAO,a.VALOR_PEDIDO,b.DATA_ENTRADA,b.DATA_PAGAMENTO, b.VALOR_PAGAMENTO
from TB_PEDIDO a, TB_PAGAMENTO b
where 
    a.COD_EMPRESA=b.COD_EMPRESA AND a.COD_FORNECEDOR=b.COD_FORNECEDOR