## 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