May 4, 2005 at 4:35 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jBulinckx/indexedviewswithouterjoins.asp
May 19, 2005 at 6:40 am
First, if you created a foreign key on father_id you wouldn't be able to insert the null son record.
Second, since -255 is your default value for 'no father' in the Father table why not remove the isnull() from the view and use -255 as the value for 'no father' in the son table? Then everything works fine with an inner join and foreign key?
Edit: Oh yeah, the table name says [Family] on the create index statements.
May 19, 2005 at 12:24 pm
Peter,
You can insert a NULL row even if there's a foreign key:
use tempdb
go
create table a (aid int not null primary key)
insert a values (1)
go
create table b (aid int references a (aid))
insert b values (null)
go
select * from b
go
drop table b
drop table a
go
--
Adam Machanic
whoisactive
May 20, 2005 at 11:14 am
Hello guys thanks for the reply!
Pete,
this chunk of code works fine
...
INSERT INTO Father (Father_id, Father_name) values(-255,'No father')
...
on isnull(s.father_id, -255)=f.father_id
...
but
...
INSERT INTO Father (Father_id, Father_name) values(null,'No father')
...
on s.father_id = f.father_id
...
don´t work at all, no childs without father is show.
At the other hand i canot change the row
INSERT INTO Son values(null,'Child 0X of no father')
to
INSERT INTO Son values(-255,'Child 0X of no father')
because at my realworld problem that table contains millions of rows with null values and dont want change my old data.
Adam,
At my problem Father_id is a IDENTITY column and cannot be nullable.
Guys,
It´s just a example from real problem. Of course u can find yourself in a bit different dilema. I´m happy to share my solution and to learn from yours replys.
Jean
April 1, 2010 at 8:22 am
You don't have to use null you can insert a record with the id of zero, a negative number, etc.. still quite the rig, but might get you out of a jam.
April 23, 2013 at 5:03 am
Your example doesn't work.
In the left and right joins, Father 3 is displayed.
With the isnull(....), there is no child record in the son table to apply the isnull too, therefore, the father record never appears.
July 17, 2013 at 2:35 pm
This is my issue as well. I need Father3 displayed in the final result set.
In my "real world problem" I need to list the fathers with no children, not the children with no fathers.
September 5, 2014 at 9:53 pm
bvrolyk (7/17/2013)
This is my issue as well. I need Father3 displayed in the final result set.In my "real world problem" I need to list the fathers with no children, not the children with no fathers.
You might try
Inner Join IsNull(c.parentId, p.ParentId) = p.ParentId with a null value in the child table. I've tried it, and it's very slow as a select, making me reluctant to do it for the index view--and since I actually need two left joins...I should probably go ahead and see what it does to my inserts, but at the moment I'm afraid the index will drag that down to an unacceptable level.
October 3, 2014 at 3:40 am
Hi, thanks for this post,
i have a question , can i create an incremental variable and make it at the place of -255 in this script:
SELECT f.father_id, f.father_name, s.father_id as son_id, s.paternity
from [dbo].[father] f
INNER JOIN [dbo].[son] s
on isnull(s.father_id, -255)=f.father_id
GO
so this column will be a unique column,
thx in advance
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply