Query two tables without foreign key and relationship

Question

Query two tables without foreign key and relationship

Navigation

#1 by (0 votes)
#2 by (0 votes)

-1

I have two tables tb_pedido and tb_pagamento , I put an example below with some dummy data. I need a report that shows the data of tb_pedido and tb_pagamento , all in a single table as in example 3. I thought about doing a left join but I could not, I just need to add the column data_pagamento and valor_pagamento to finalize 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           |

mysql sql

asked by anonymous 28.11.2018 / 19:06

2 answers

How to change the name of the button if the user has registered - LARAVEL? I can not compare the strings

score 0 · Answer 1

First I created the two tables and entered the data as it was posted in the question ( Remember to post in the next few questions, it makes it easier for anyone to 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 from the tb_pedido and the 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');

Output 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 that should be implemented according to your application.

Remembering : Foreign keys ensure the integrity of the stored data (One of the pillars of MySQL). Using this way, you can delete data from a table that would be associated with another table ... Still not a good practice, it is not the purpose 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

score 0 · 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