Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
obiron
Starting Member
23 Posts |
 Posted - 2009-08-28 : 08:14:45
|
| Hi guys, This one has me stumped.SQL2005.I have a table that lists DQ issues in the system.The table has a columns for ReportDate, ErrorCode, A,B,C,D where A-D are values in other tables that allow me to find the correct record details. A is always completed. Always as least one of B,C and D are completed, but none, one or two of them could be NULL.I want to do a full outer join of the table to itself, choosing records with a ReportDate = getdate() and ReportDate = dateadd(day,getdate(),-7)The problem is that I need to match all 4 fields in both sides of the joins, but because they may contain NULL I am not getting the correct results back. Where B,C, and D are completed, I get back a single record with the left and right tables correctly displayed. Where one or more fields is NULL, the left and right tables show in two separate lines.If necessary I will produce the results set using UNION and set the criteria for each SELECT to ignore records with NULL columns but this requires 7 union'ed statements and I am sure there must be a more elegant way... |
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-08-28 : 10:53:09
|
| Haha, I had this same question a week or so ago (well sorta)I have 2 department fields that when they designed the app they were not made as mandatory so can be null:ON IsNULL(L1.Create_Dept, 'None') = IsNULL(L2.Create_Dept, 'None') And IsNULL(L1.Refer_Dept, 'None') = IsNULL(L2.Refer_Dept, 'None') John"The smoke monster is just the Others doing barbecue" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
join a table to itself NULL values in join cols