NULL the duplicate values of a LEFT JOIN Msql query

Question

NULL the duplicate values of a LEFT JOIN Msql query

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (1 votes)
#4 by (1 votes)

6

I have 2 tables.
The first returns the order number and the two-state times

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

The second one, 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 making the following query:

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

That 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 that for the duplicate values, it shows 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

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

Thanks for your help.

mysql sql query mysql-workbench join

asked by FacundoInsua 03.05.2018 в 19:21

source

4 answers

1

This will surely have some syntax error, but maybe it can help you to direct your idea

SELECT * --Aquí los campos que te interese mostrar
FROM tabla1 A
INNER JOIN( select B.pedido, SUM(B.TiempoEmpacar), SUM(TiempoEnruta )
            from tabla2 B 
            GROUP BY B.pedido) on A.pedido = pedido

In the subquery we add the times and group them by request and in the main query we show the times already grouped

answered by 03.05.2018 в 19:40

1

To achieve the expected result, based on this answer from OS in English.

You could make subqueries to get the rows of interest in table2 by setting a pseudo-order based on TiempoEmpacar and TiempoEnruta :

Assuming TiempoEnruta less than 1000
For join with tabla1 , use that row with less TiempoEmpacar and TiempoEnruta determined by the expression
```
t.TiempoEmpacar * 1000 + t.TiempoEnruta
```
Then join the remaining rows of tabla2

Remaining a fairly extensive query:

select A.*, B.TiempoEmpacar, B.TiempoEnruta 
from tabla1 A
left join (
  select t.Pedido, t.TiempoEmpacar, t.TiempoEnruta
  from tabla2 t join tabla2 tt
  on t.Pedido = tt.Pedido
  and t.TiempoEmpacar * 1000 + t.TiempoEnruta <= tt.TiempoEmpacar * 1000 + tt.TiempoEnruta
  group by t.Pedido, t.TiempoEmpacar, t.TiempoEnRuta
  having count(*) = 1
) B
on A.pedido = B.pedido

union

select t.Pedido, null, null, t.TiempoEmpacar, t.TiempoEnruta
from tabla2 t join tabla2 tt
on t.Pedido = tt.Pedido
and t.TiempoEmpacar * 1000 + t.TiempoEnruta <= tt.TiempoEmpacar * 1000 + tt.TiempoEnruta
group by t.Pedido, t.TiempoEmpacar, t.TiempoEnRuta
having count(*) > 1

order by pedido;

You can try the above in SQLfiddle

answered by 03.05.2018 в 20:36

1

It is an interesting problem, in the following query you can get the result you are looking for, use a user variable to identify the first order and make the join using it.
This query assumes that table2 is ordered by order number, otherwise a subquery would be required to order it.

  SELECT B.pedido,
         A.TiempoPreparar,
         A.TiempoAsignar,
         B.tiempoEmpacar,
         B.TiempoEnruta
    FROM tabla1 A RIGHT JOIN 
       ( SELECT pedido,
                IF(@pedido = pedido, NULL, @pedido:= pedido) primerPedido,
                TiempoEmpacar,
                TiempoEnruta 
           FROM tabla2
              ) B ON  A.pedido = B.pedido
                  AND B.primerPedido IS NOT NULL
ORDER BY B.pedido,A.Pedido DESC;

answered by 03.05.2018 в 20:40

Error compiling com.google.gms: play-services-ads: 9.2.0 Apply css to a parent element

score 2 · Accepted Answer

If it is to avoid repeated you 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;