CODEWITHSUNDEEP
sqlsql-server
Ad van Dorpen
Ad van Dorpen

Reputation: 56

Using COALESCE within a FULL JOIN, returning duplicate values

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?

Upvotes: 0

Views: 1223

Answers (1)

D&#225;vid Laczk&#243;
D&#225;vid Laczk&#243;

Reputation: 1101

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.

Upvotes: 1

Related Questions