Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexed Views With Outer Joins


Indexed Views With Outer Joins

Author
Message
jcb
jcb
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 969
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jBulinckx/indexedviewswithouterjoins.asp
peterlemonjello
peterlemonjello
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
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.



Adam Machanic
Adam Machanic
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1203 Visits: 714
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
jcb
jcb
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2750 Visits: 969

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


Steve Stocker
Steve Stocker
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 48
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.
Pridmore_James
Pridmore_James
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
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.
bvrolyk
bvrolyk
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 16
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.
john.moreno
john.moreno
Old Hand
Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)

Group: General Forum Members
Points: 381 Visits: 1112
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.
h2sh
h2sh
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 25
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search