# I need the identical lines to merge

## All we need is an easy explanation of the problem, so here it is.

Previous question: Return row with values if nothing fits the conditions

I almost got this working.
The problem that I have is that this variant of query does not merge the line from the SELECT with the one in the UNION if they are the same:

``````SELECT CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM
FROM FGE50LM0TV.GESUPD
WHERE CNFLIG = '2'
AND CODPRO IN (SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59)
GROUP BY CODACT, CODPRO, VALPRO
UNION (SELECT VALINV, 0, 0 FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59) ;
``````

This is what SVINVD looks like:

This is what GESUPD looks like:

No line for "PRODUIT5" here.

So in consequence I get a doubled line:

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

The `UNION` operator eliminates duplicate rows. It means that two rows will merge only if they are completely identical. Row

CODPRO VALPRO SUM
PRODUCT3 0 212

will not merge with row

CODPRO VALPRO SUM
PRODUCT3 0 0

because the values in the last column differ.

Anyway, if the goal is to complete the set returned by

``````SELECT CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM
FROM FGE50LM0TV.GESUPD
WHERE CNFLIG = '2'
AND CODPRO IN (SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59)
``````

with missing values found in

``````SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59
``````

then there are a few approaches to this.

### 1. `UNION` + `EXCEPT`

``````SELECT CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM
FROM FGE50LM0TV.GESUPD
WHERE CNFLIG = '2'
AND CODPRO IN (SELECT VALINV FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59)
UNION
(
SELECT VALINV, 0, 0 FROM FGE50LM0TV.SVINVD WHERE NUMINV = 59
EXCEPT
SELECT CODPRO, 0, 0 FROM FGE50LM0TV.GESUPD WHERE CNFLIG = '2'
)
``````

There are repetitive bits of logic here, like the `CNFLIG = '2'` – an aspect which I personally prefer to avoid in my queries if possible. Other than that, this seems like more or less straightforward approach to me.

### 2. `LEFT JOIN`

Use your query as a derived table and outer-join it to `FGE50LM0TV.SVINVD`. Substitute 0 for missing rows’ `VALPRO` and `SUM`. Like this:

``````SELECT
s.VALINV AS CODPRO, COALESCE(g.VALPRO, 0) AS VALPRO, COALESCE(g.SUM, 0) AS SUM
FROM
FGE50LM0TV.SVINVD AS s
LEFT JOIN
(
SELECT
CODPRO, VALPRO, COALESCE(SUM(UVCLIV), 0)  AS SUM
FROM
FGE50LM0TV.GESUPD
WHERE
CNFLIG = '2'
) AS g ON s.VALINV = g.CODPRO
WHERE
s.NUMINV = 59
;
``````

### 3. `LEFT JOIN`, Alternative Option

You can also try outer-joining `FGE50LM0TV.GESUPD` to `FGE50LM0TV.SVINVD` directly, and then apply the grouping:

``````SELECT
s.VALINV AS CODPRO, COALESCE(g.VALPRO, 0) AS VALPRO, COALESCE(SUM(g.UVCLIV), 0) AS SUM
FROM
FGE50LM0TV.SVINVD AS s
LEFT JOIN FGE50LM0TV.GESUPD AS g ON s.VALINV = g.CODPRO AND g.CNFLIG = '2'
WHERE
s.NUMINV = 59
GROUP BY
s.VALINV, g.VALPRO
;
``````

Note that the `CNFLIG = '2'` is moved from `WHERE` to the `ON` clause. This is so as to avoid eliminating non-matching `GESUPD` entries. The `WHERE` clause applies to the entire joined set, and where `GESUPD` has no match for `SVINVD`, the `CNFLIG = '2'` would completely exclude that row from the output, contrary to what you are trying to achieve. In the `ON` clause, however, the condition would apply to `GESUPD` only, and `SVINVD` rows that have no match would still be returned.

While generally looking simpler than the other `LEFT JOIN` solution, this option may not necessarily be as fast. You will have to test both to compare the performance for yourself. Naturally, that goes for all three options.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0