problem with a view, its not bring back rows with null in it
-
Hi Ben,
Try using 'left outer join' instead of 'inner join'...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ben,
I solved this problem by using the isnull function
JOIN... ON ISNULL(table1.nullable_ID, 0) = ISNULL(dbo.table2.nullable_ID, 0)
Hope this helps
Pieter
-
Pieter - Why would you do that rather than simply a left outer join?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan,
In my particular case i am actually using left outer join, but have two IDs to join too, one PK, and another a nullable FK. So the join would actually look more like..
FROM dbo.Claim_Items LEFT OUTER JOIN
dbo.vClaimSecurityEvents ON dbo.Claim_Items.MaintChargeItem_ID = dbo.vClaimSecurityEvents.MaintChargeItem_ID AND
ISNULL(dbo.Claim_Items.MaintRateSplit_ID, 0) = ISNULL(dbo.vClaimSecurityEvents.MaintRateSplit_ID, 0)
If i left out the ISNULL then all records where the nullable FK was null did not return. If you have a better way please let me know
Regards
Pieter
-
Pieter,
I can't get my head around it without some sample data, but surely if you use a full outer join and no where clause, nothing will be left out? Is that not right?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan,
Without going in to much detail...
Got claim item that have security events, maintenance events, lessor contributions and contract securities. They all have the same grouping of FK (not nullable) and FK (with FK nullable), as well as individual PK's to join with claim items. The full join statement is below, i just cut off the select.
FROM dbo.Claim_Items LEFT OUTER JOIN
dbo.vClaimSecurityEvents ON dbo.Claim_Items.MaintChargeItem_ID = dbo.vClaimSecurityEvents.MaintChargeItem_ID AND
ISNULL(dbo.Claim_Items.MaintRateSplit_ID, 0) = ISNULL(dbo.vClaimSecurityEvents.MaintRateSplit_ID, 0) AND
dbo.Claim_Items.SecurityObligation_ID = dbo.vClaimSecurityEvents.SecurityObligation_ID LEFT OUTER JOIN
dbo.vClaimMREvents ON ISNULL(dbo.Claim_Items.MaintRateSplit_ID, 0) = ISNULL(dbo.vClaimMREvents.MaintRateSplit_ID, 0) AND
dbo.Claim_Items.MaintChargeItem_ID = dbo.vClaimMREvents.MaintChargeItem_ID LEFT OUTER JOIN
dbo.vClaimLCEvents ON ISNULL(dbo.Claim_Items.MaintRateSplit_ID, 0) = ISNULL(dbo.vClaimLCEvents.MaintRateSplit_ID, 0) AND
dbo.Claim_Items.MaintChargeItem_ID = dbo.vClaimLCEvents.MaintChargeItem_ID AND
dbo.Claim_Items.Contribution_ID = dbo.vClaimLCEvents.Contribution_ID LEFT OUTER JOIN
dbo.vClaimSecurityContract ON dbo.Claim_Items.SecurityObligation_ID = dbo.vClaimSecurityContract.SecurityObligation_ID
If I am understanding you correctly you are saying I should be able to do this with only a full outer join and not the left outer joins?? How can I then link the rows in Claim_Items with their corresponding records in the joined views?
Regards
Pieter
-
Pieter - As I say, I can't get my head around your situation without some sample data. I guess if it's working for you, then don't worry.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Using FULL JOIN will include rows where the join field is NULL, but they will be in different records. Assuming tables A and B are joined on field XYZ and both tables have exactly one record where XYZ = NULL, the FULL JOIN will return:
A.F1, A.F2, ..., NULL, NULL, ...
NULL, NULL, ..., B.F1, B.F2, ...
whereas if you join with ON ISNULL(A.XYZ, 0) = ISNULL(B.XYZ,0) you'll get one record.
If there are multiple records with NULLs in the source table join columns they'll be cross joined, and the ISNULL function will interfere with using indexes, but if that's what you want then that's the way to do it.
-
used RyanRandall's suggestion and it worked great!
thankyou
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply