Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexed Views With Outer Joins Expand / Collapse
Author
Message
Posted Wednesday, May 4, 2005 4:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:45 AM
Points: 2,693, Visits: 897
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jBulinckx/indexedviewswithouterjoins.asp
Post #180031
Posted Thursday, May 19, 2005 6:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 10:17 AM
Points: 213, Visits: 3
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.



Post #183664
Posted Thursday, May 19, 2005 12:24 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #183842
Posted Friday, May 20, 2005 11:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:45 AM
Points: 2,693, Visits: 897

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

Post #184219
Posted Thursday, April 1, 2010 8:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 10:04 AM
Points: 1, Visits: 38
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.
Post #894821
Posted Tuesday, April 23, 2013 5:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 24, 2013 3:18 AM
Points: 84, Visits: 185
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.
Post #1445357
Posted Wednesday, July 17, 2013 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 2:32 PM
Points: 6, Visits: 15
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.
Post #1474802
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse