CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
Martin Brown
Martin Brown

Reputation: 25310

How to Join to "Other" row

I have two tables. One holds Objects and the other holds Settings about each object. Not all of the rows in the Objects table have a corresponding row in the Settings table. There is a special row in the Settings table that is supposed to be used for the "Other" objects.

How can I create a join between Objects and Settings such that I get the given setting if there is one or the "Other" setting if there isn't?

For example consider the following script:

CREATE TABLE #Objects (Code nvarchar(20) not null);
CREATE TABLE #Settings (Code nvarchar(20) not null, Value int not null);

INSERT INTO #Objects
VALUES
    ('A'),
    ('B'),
    ('D')

INSERT INTO #Settings
VALUES
    ('A', 1),
    ('B', 2),
    ('C', 3),
    ('Other', 4)

SELECT 
    #Objects.Code,
    #Settings.Value 
FROM
    #Objects
    JOIN #Settings
        ON #Objects.Code = #Settings.Code
        OR #Settings.Code = 'Other'

DROP TABLE #Settings, #Objects

I'm wanting to get this:

Code | Value
---- | -----
A    | 1
B    | 2
D    | 4

What I'm actually getting is:

Code  | Value
----- | -----
A     | 1
A     | 4
B     | 2
B     | 4
D     | 4

Upvotes: 2

Views: 73

Answers (4)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

If there is going to be one "Other" value then you can just do the join twice - a left join and another one which is effectively a cross join:

select o.Code,
    coalesce(s.Value, s2.value) as value
from #Objects o
left join #Settings s on o.Code = s.Code
join #Settings s2 on s2.Code = 'Other'

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415620

You can do this with an APPLY:

SELECT o.Code, s.Value
FROM #Objects o
CROSS APPLY (
    SELECT TOP 1 * 
    FROM #Settings s 
    WHERE s.Code = o.Code or s.Code = 'Other' 
    ORDER BY case when s.Code = o.Code then 0 else 1 end
) s

For fun: a hybrid from answers by Gurv, jyao and SqlZim, which are all variations on the same basic theme:

SELECT o.Code, s2.Value
FROM #Objects o
LEFT JOIN #Settings s1 on s1.Code = o.Code
INNER JOIN #Settings s2 on s2.Code = coalesce(s1.Code, 'Other')

So far, this approach (LEFT JOIN + the INNER JOIN ON COALESCE() ) is my favorite option.

Note that this only works if there can be only one Settings record per Object record. If that ever changes, the APPLY answer still works, but other answers here might not work.

Upvotes: 2

SqlZim
SqlZim

Reputation: 38023

Using a left join to get null where o.Code has no match in #Settings , and using coalesce() to return the designated replacement value from #Settings when s.Value is null.

You could use isnull() instead of coalesce, the result would be the same in this instance.

I am not sure if this acceptable, but it returns the correct results:

select 
    o.Code
  , coalesce(s.Value,x.Value) as Value
from #Objects o
  left join #Settings s 
    on o.Code = s.Code
  cross join (
    select top 1 value
    from #Settings
    where Code = 'Other'
    ) x

rextester demo: http://rextester.com/EBUG86037

returns:

+------+-------+
| Code | Value |
+------+-------+
| A    |     1 |
| B    |     2 |
| D    |     4 |
+------+-------+

In the form @RBarryYoung prefers:

select 
    o.Code
  , coalesce(s.Value,x.Value) as Value
from #Objects o
  left join #Settings s 
    on o.Code = s.Code
  inner join #Settings x
    on x.Code = 'Other'

This is more concise (saves you many keystrokes) and generates the same execution plan as my initial answer. Whether it is more or less clear about what it is doing is up to you, I like both.

Upvotes: 1

jyao
jyao

Reputation: 1630

Another way is to use CTE to add an additional column [Alternative_code] for [#Object] table that has value "Other" for [Code] not existing in [#Settings] and then using this CTE to join with #Settings table as shown below

; with c as (
    select alternative_Code = isnull(s.code, 'Other'), o.Code
    from #Objects o
    left join #Settings s
        on o.Code = s.Code)
select c.Code, s.value
from c
inner join #Settings s
on c.alternative_Code = s.Code

Upvotes: 1

Related Questions