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;