Using COALESCE within a FULL JOIN, returning duplicate values

Question

I am trying to link different keys together. The following problem the projid key has to be joint on ServiceObjectId and on TaskId. The easiest way to do this seems to use an or statement.

So:

FULL JOIN PROJTABLE
    ON PROJTABLE.PROJID = MSM_TASKTABLE.PROJID
    or PROJTABLE.PROJID = MSM_SERVICEOBJECTTABLE.SERVICEOBJECTID

However the performance is dreadful Thats way i tried to use the COALESCE function. As shown in the following script

INSERT INTO AR_BI_sleutels (DataareaId,PartyId,ContractId,RentalObjectId,ObjectId,ServiceObjectId,ServiceCallId,TaskId,ProjId)
Select
    ,PMCCONTRACT.CONTRACTID
    ,PMCCONTRACTOBJECT.RENTALOBJECTID
    ,PMEOBJECT.OBJECTID
    ,MSM_SERVICEOBJECTTABLE.SERVICEOBJECTID
    ,MSM_SVCCALLTABLE.SVCCALLID
    ,MSM_TASKTABLE.TASKID
    ,COALESCE(TASK.PROJID,SERVICE.PROJID) AS PROJID
From PMCCONTRACT
FULL JOIN PMCCONTRACTOBJECT
    ON PMCCONTRACTOBJECT.RENTALOBJECTID = PMCCONTRACT.RENTALOBJECTID
        AND PMCCONTRACTOBJECT.DATAAREAID = PMCCONTRACT.DATAAREAID
FULL JOIN MSM_SERVICEOBJECTTABLE
    ON MSM_SERVICEOBJECTTABLE.EXTOBJECTID = PMEOBJECT.OBJECTID
FULL JOIN MSM_SVCCALLTABLE
    ON MSM_SVCCALLTABLE.SERVICEOBJECTID = MSM_SERVICEOBJECTTABLE.SERVICEOBJECTID
FULL JOIN MSM_TASKTABLE
    ON MSM_TASKTABLE.SVCCALLID = MSM_SVCCALLTABLE.SVCCALLID
FULL JOIN PROJTABLE as Task
    ON Task.PROJID = MSM_TASKTABLE.PROJID
FULL JOIN PROJTABLE as Service
    ON service.PROJID = MSM_SERVICEOBJECTTABLE.SERVICEOBJECTID
WHERE (PMCCONTRACT.CONTRACTSTATUS is null OR PMCCONTRACT.CONTRACTSTATUS <> 5)
        AND COALESCE(TASK.PROJID,SERVICE.PROJID) IS NOT NULL

However this statement returns duplicate values. Any ideas on how i could make the working correctly?

Personally, I wouldn't use `COALESCE` in the `WHERE`, I would use boolean logic: `AND (TASK.PROJID IS NOT NULL OR SERVICE.PROJID IS NOT NULL)`. Your `WHERE` isn't going to be creating duplicates, a `WHERE` filters results not adds them. I suspect the problem is that you are using `FULL JOIN`s everywhere; so the entire contents of every table is returned and the only filtered based on your `WHERE`. Which raises the question, why *are* you using `FULL OUTER JOIN`s? It may well be the problem goes away if you use an `INNER JOIN` or `LEFT JOIN`. Impossible to tell with sample and expected data. — Thom A, Dec 16 '18 at 18:34
Please provide sample data and desired results -- preferably with a simplified query. `FULL JOIN`s are tricky, particularly when trying to filter the data. They are almost never actually needed. — Gordon Linoff, Dec 16 '18 at 18:41
Perhaps this answer will help you understand different types of `JOIN`: https://stackoverflow.com/a/16598900/3484879 — Thom A, Dec 16 '18 at 18:41
I have never seen so many full joins, they aren't used very often. How can you have a "contract object" without a "contract"? (that is probably an inner join, not a full join). Without sample data (from each table, individually) we can do very little to help. — Paul Maxwell, Dec 16 '18 at 22:43
additionally the query you have supplied will not work, there is a spurious comma after select: `select , PMCCONTRACT.CONTRACTID` and there is no join for `PMEOBJECT` — Paul Maxwell, Dec 16 '18 at 22:50

Dávid Laczkó · Answer 1 · 2018-12-16T19:42:42.400

FULL JOIN is an OUTER JOIN type which means keep rows from both sets even if the lookup condition is not satisfied. Originally, with PROJTABLE FULL JOIN'd once, you have fed all rows of PROJTABLE once to the final result. You changed the logic by FULL JOINing PROJTABLE twice, so you have fed all rows of PROJTABLE twice to the final result. Now I guess that using COALESCE you thought that it is similar to an OR condition in your original lookup (or see my PS), but it will not do what needs to be done now: you don't want to keep all rows of PROJTABLE twice. You need to use distinct in the final query, or in a subquery that collects rows only from PROJTABLE, or you might think over if you really need all rows from PROJTABLE and maybe change the JOIN type.

PS: COALESCE(TASK.PROJID,SERVICE.PROJID) IS NOT NULL states remove from the final result if there were neither a TASK nor a SERVICE match (because NULL can only be returned by COALESCE if all args are null) - it is not your original statement.

Using COALESCE within a FULL JOIN, returning duplicate values

1 Answers1