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.
If it is to avoid repetitions, have a
union
instead of ajoin
This surely has some syntax error, but maybe it can help you to direct your idea
In the subquery we add the times and group them by order and in the main query we show the times already grouped
To achieve the expected result, based on this SO answer in English.
You could perform subqueries to get the rows of interest from table2 by setting a pseudo-order based on
TiempoEmpacar
yTiempoEnruta
:TiempoEnruta
less than 1000For the
join
withtabla1
, use that row with less thanTiempoEmpacar
andTiempoEnruta
determined by the expressiontabla2
Leaving a fairly extensive query:
You can try the above in SQLfiddle
It's an interesting problem, in the following query you can get the result you are looking for, use a user variable to identify the first request and perform the join using it.
This query assumes that table2 is sorted by order number, otherwise a subquery would be needed to sort it.