CODEWITHSUNDEEP
sql-server-2008
John
John

Reputation: 829

Handling all possible values for null column in SQL Server 2008

I have struggled with this issue for several weeks. I have Googled it and have zero relevant results (which probably means I can't figure out how to Google it - not that the answer isn't out there!)

Assume four tables

Table 1 - templates 

Template 1...  
Template 2...  
Template 3...  
Template 4...

Table 2 - Periods 

Period 1...  
Period 2...  
Period 3...  
Period 4...

Table 3 - Type 

Type 1...  
Type 2...

Table 4 - TemplateMatrix

Contains the matrix for which templates are applicable to which period and type. Most of the templates are applicable to all periods except 1 and there are specific templates for that period. Rather than create a templateMatrix record for every template for every period, I create one record for each specific period template with that periodID

EX: 

PeriodID,   TypeID,  TemplateID  
1       ,   1     ,  1
2       ,   1     ,  2

and a record with a NULL for PeriodID for all other periods for their templates.

EX: 

PeriodID,   TypeId, TemplateID  
NULL    ,   1,      3  
NULL    ,   2,      4

There are actually about 40 Periods and about 8 Templates and 2 types.

What I need is a view that will give me all possible Template and Period combinations- those that are expressly defined and all others.

In other words, 

Period 1, Type 1, Template 1  
Period 2, Type 1, Template 2  
Period 3, Type 1, Template 3  
Period 3, Type 2, Template 4  
Period 4, Type 1, Template 3  
Period 4, Type 2, Template 4  
Period 5, Type 1, Template 3  
Period 5, Type 2, Template 4  
Period 6, Type 1, Template 3  
Period 6, Type 2, Template 4

Worst case scenario, I could add all possible records to the matrix; however, that doesn't seem very normalized to me since the only difference between the bulk of the records would be the Period ID.

Any ideas? I really am at my wits end.

Thanks, John

Upvotes: 1

Views: 689

Answers (2)

nybbler
nybbler

Reputation: 4841

The following select should work for you: 

SELECT DISTINCT p.PeriodID, ty.TypeID, tmpl.TemplateID 
FROM TemplateMatrix AS matrix 
JOIN Periods AS p ON 
   (
       -- Defined Periods
       (matrix.PeriodID = p.PeriodID) OR 
       -- Undefined Periods, excluding defined cases
       (
           IsNull(matrix.PeriodID, p.PeriodID) = p.PeriodID 
           AND p.PeriodID NOT IN (SELECT DISTINCT defMtrx.PeriodID FROM TemplateMatrix AS defMtrx)
       )
   )
JOIN Type AS ty ON (matrix.TypeID = ty.TypeID) 
JOIN Templates AS tmpl ON (matrix.TemplateID = tmpl.TemplateID)

This should only map NULL for PeriodID to the values for Period that aren't explicitly defined in your TemplateMatrix table, and use the defined combinations for Period/Type/Template when available.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

You've struggled for weeks with a problem that could be solved by simply storing important business data directly in a table. We're talking about, at most, around 360 rows, right? Just insert the rows. Jeez.

The root cause of your problem is that a) you're trying to shoehorn two predicates into one table, and b) you're hoping to redefine what NULL means.

PeriodID,   TypeID,  TemplateID  
1       ,   1     ,  1
NULL    ,   1     ,  3

You want the first row to mean something like this:

Template [TemplateID] is used in period [PeriodID] for something of type [TypeID]

But you want the second row to mean something like

Template [TemplateID] is used in every period that hasn't been identified by [PeriodID] in some other row for something of type [TypeID]

Trying stuff like that will almost always lead to grief.

Upvotes: 2

Related Questions