CODEWITHSUNDEEP
sqlsql-servert-sql
user3478586
user3478586

Reputation: 317

TSQL Return all matching and unmatching rows

This is what I have to work with.

Table A
-------
UnitID   NamedValue   Value
Null     TestA        11
Null     TestB        30
1        TestA        20
1        TestB        15
2        TestA        18

Table A contains all defined values AND default values if a UnitID has not been set. So UnitID 1 should pull up TestA value of 20 and TestB with a value of 15. UnitID 2 should pull up TestA value of 18 and the default value TestB value 30

Results should look like the following:

UnitID   NamedValues   Value
1        TestA         20
1        TestB         15
2        TestA         18
2        TestB         30

I didn't design the thing but I have to work with it. Please help me with a join that will provide the results needed. I can do it with temp tables but was hoping for a simple join.

Upvotes: 0

Views: 62

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

This query finds all combinations of UnitID and NamedValue, and fills all missing values with the default. Not the most beautiful query, but it would seem to do the job;

WITH cte AS (
  SELECT DISTINCT a.UnitID, b.NamedValue
  FROM Table_A a CROSS JOIN Table_A b
  WHERE a.UnitID IS NOT NULL
)
SELECT cte.UnitID, cte.NamedValue, COALESCE(c.Value, d.Value) Value
FROM cte
LEFT JOIN Table_A c ON cte.UnitID = c.UnitID AND cte.NamedValue = c.NamedValue
LEFT JOIN Table_A d ON d.UnitID IS NULL AND cte.NamedValue = d.NamedValue

An SQLfiddle to test with.

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56735

This should do it:

SELECT a.UnitID, v.NamedValues, COALESCE(v.Value, d.Value) As Value
FROM (Select 'TestA' As Nam UNION ALL Select 'TestB') v
LEFT OUTER JOIN 
     (Select UnitID, NamedValues, MAX(Value) As Value 
      From TableA 
      Group By UnitID, NamedValues
     ) a
    ON v.Nam = a.NamedValues
LEFT OUTER JOIN 
     (Select UnitID, NamedValues, MAX(Value) As Value 
      From TableA 
      Where UnitID IS NULL
      Group By UnitID, NamedValues
     ) d
    ON v.Nam = d.NamedValues

Upvotes: 0

Related Questions