CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
paparazzo
paparazzo

Reputation: 45106

Find null or value(s)

Join to a table that stores multivalue information
Need to query for null or value(s)
Currently do it with a union
First get the null with an outer join
Second union to get the values Is there a better approach? 

select [docSVsys].[sID] 
  from [docSVsys] with (nolock) 
  left outer join [docEnum1] as [jointable] with (nolock) 
    on [jointable].[enumID] = '142' 
   and [jointable].[sID] =  [docSVsys].[sID] 
 where [jointable].[sID] is null 
Union 
Select distinct([Table].[sID]) 
  From  [DocEnum1] as [Table] with (nolock) 
 Where  1 = 1
   And  [Table].[enumID] = '142' 
   and  [Table].[valueID] in (1,2)

Upvotes: 1

Views: 83

Answers (1)

Joe Taras
Joe Taras

Reputation: 15399

Try this:

select distinct
case
    when [jointable].[valueID] in (1,2) then [jointable].[sID]
    else [docSVsys].[sID] 
end
from [docSVsys] with (nolock) 
left outer join [docEnum1] as [jointable] with (nolock) 
    on [jointable].[enumID] = '142' 
    and [jointable].[sID] =  [docSVsys].[sID] 
where [jointable].[sID] is null or [jointable].[valueID] in (1,2)

Tell me if it's OK

Upvotes: 1

Related Questions