SQLServerCentral / Discuss Content Posted by Jean Charles Bulinckx / Article Discussions / Article Discussions by Author / Indexed Views With Outer Joins / Latest Posts

RE: Indexed Views With Outer Joins

Pridmore_James — Tue, 23 Apr 2013 05:03:07 GMT

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.

RE: Indexed Views With Outer Joins

Steve Stocker — Thu, 01 Apr 2010 08:22:38 GMT

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.

RE: Indexed Views With Outer Joins

jcb — Fri, 20 May 2005 11:14:00 GMT

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')

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

RE: Indexed Views With Outer Joins

Adam Machanic — Thu, 19 May 2005 12:24:00 GMT

Peter,You can insert a NULL row even if there's a foreign key:use tempdbgocreate table a (aid int not null primary key)insert a values (1)gocreate table b (aid int references a (aid))insert b values (null)goselect * from bgodrop table bdrop table ago

RE: Indexed Views With Outer Joins

peterlemonjello — Thu, 19 May 2005 06:40:00 GMT

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.

Indexed Views With Outer Joins

jcb — Wed, 04 May 2005 16:35:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jBulinckx/indexedviewswithouterjoins.asp