mysql – Null duplicate values ​​in a LEFT JOIN Msql query

Question:

I have 2 tables.
The first returns the order number and the times of two states

Pedido     TiempoPreparar     TiempoAsignar
-------------------------------------------
P001         10                9
P002         20                5
P003         30                7

The second returns the times of 2 different states:

Pedido     TiempoEmpacar  TiempoEnruta
-------------------------------------------
P001         23                10
P001         14                21
P002         34                6
P003         16                5
P003         22                12

In table 2 there may be several records for a single order.

I am performing the following query:

select A.*,B.TiempoEmpacar, TiempoEnruta 
from tabla1 A 
left join tabla2 B
on A.pedido = B.pedido

which returns this

Pedido TiempoPreparar TiempoAsignar TiempoEmpacar TiempoEnRuta
-------------------------------------------------------------
P001        10             9              23           10
P001        10             9              14           21
P002        20             5              34           6
P003        30             7              16           5
P003        30             7              22           12

But I need for duplicate values ​​to return NULL, something like this:

Pedido TiempoPreparar TiempoAsignar TiempoEmpacar TiempoEnRuta
-------------------------------------------------------------
P001        10             9              23           10
P001        NULL           NULL           14           21
P002        20             5              34           6
P003        30             7              16           5
P003        NULL           NULL           22           12

I take that data to Quicksight (reporting tool), where I create a Dashboard, there I have the option to make AVERAGES, but if I have duplicate data it will take it into account, and that is what I want to avoid.

Thanks for your help.

Answer:

If it is to avoid repetitions, have a union instead of a join

SELECT
    Pedido,
    TiempoPreparar,
    TiempoAsignar,
    null as TiempoEmpacar,
    null as TiempoEnruta
FROM tabla1
UNION ALL
SELECT
    Pedido,
    null as TiempoPreparar,
    null as TiempoAsignar,
    TiempoEmpacar,
    TiempoEnruta
FROM tabla2
ORDER BY Pedido;
Scroll to Top