Eliminating null values in union-CodePudding

I'm doing a query across databases with an identical structure, to show a mapping from a source value to a target value.
Every one of my databases has a table with two columns: source and target

DB1

Source	Target
A	X
A	Y
B	NULL
C	NULL

DB2

Source	Target
A	NULL
A	Y
B	Z

So my query is

Select t.Source, t.Target
from DB1.table t
union
Select t.Source, t.Target
from DB2.table t

What I'm getting is

Source	Target
A	X
A	Y
B	NULL
C	NULL
B	Z
A	NULL

But I'm only interested in the target being NULL, if there is no other mapping present. So I'm looking for the following result:

Source	Target
A	X
A	Y
C	NULL
B	Z

How can I easily eliminate the highlighted rows A | NULL and B | NULL from my results?

I've seen a few answers suggesting using MAX(Target), but that won't work for me since I can have multiple valid mappings for a single source (A | X and A | Y)

CodePudding user response：

Something like this would work, just give a number based on NULL, and select the first:

SELECT  TOP(1) WITH TIES UN.Source
,       UN.Target
FROM    (
            Select t.Source, t.Target
            from DB1.table t
            union
            Select t.Source, t.Target
            from DB2.table t
        ) AS UN
ORDER BY DENSE_RANK()OVER(PARTITION BY UN.Source ORDER BY CASE WHEN UN.Target IS NOT NULL THEN 1 ELSE 2 END)

CodePudding user response：

You might find it easier to think in terms of minimums:

with data as (
    select Source, Target from DB1.<table> union
    select Source, Target from DB2.<table>
), qualified as (
    select *,
        case when Target is not null or min(Target) over (partition by Source) is null
             then 1 end as Keep
    from data
)
select Source, Target from qualified where Keep = 1;