problem with a view, its not bring back rows with null in it
-
hi,
i have a view that brings back the history of bookings from a table. 2 columns in the table allow for null entries to be entered (the null specifies they have no preference with this column), the trouble is the view will only show a row if BOTH of these values are NOT null.
This is causing me a lot of grief at the moment because half of the data is not being returned!
to illustrate the setup i have a have uploaded this picture of the view...
http://img96.imageshack.us/my.php?image=view1ri.jpg
the columns that can be null are dbo.BEN_CONSULTANCY.ConsultancyID and dbo.BEN_CONSULTANCY.ClientID. is there anyway at all i can alter the code to allow for nulls to show in the view?
many thanks,
Ben
SELECT dbo.BEN_CONSULTANCY.ConsultancyID, dbo.BEN_CONSULTANCY.ConsultancyBookingNumber, dbo.BEN_CONSULTANCY.ClientID,
dbo.BEN_CLIENT.ClientName, dbo.BEN_CONSULTANCY.ConsultantID, dbo.BEN_CONSULTANT.ConsultantName,
dbo.BEN_CONSULTANCY.ConsultancyTypeID, dbo.BEN_CONSULTANCY_TYPE.ConsultancyTypeName, dbo.BEN_CONSULTANCY.ConsultancyLevel,
dbo.BEN_CONSULTANCY.ConsultancyDate, dbo.BEN_CONSULTANCY.ConsultancyStateID, dbo.BEN_CONSULTANCY.TimeAndDate
FROM dbo.BEN_CONSULTANCY INNER JOIN
dbo.BEN_CONSULTANCY_TYPE ON dbo.BEN_CONSULTANCY.ConsultancyTypeID = dbo.BEN_CONSULTANCY_TYPE.ConsultancyTypeID INNER JOIN
dbo.BEN_CONSULTANT ON dbo.BEN_CONSULTANCY.ConsultantID = dbo.BEN_CONSULTANT.ConsultantID INNER JOIN
dbo.BEN_CLIENT ON dbo.BEN_CONSULTANCY.ClientID = dbo.BEN_CLIENT.ClientID
-
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply