Indexed Views With Outer Joins

  • jcb

    SSCertifiable

    Points: 6574

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

  • peterlemonjello

    SSC Veteran

    Points: 249

    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

    SSCoach

    Points: 15259

    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

  • jcb

    SSCertifiable

    Points: 6574

    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

    SSC Rookie

    Points: 25

    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

    SSC-Addicted

    Points: 430

    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

    SSC Rookie

    Points: 28

    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

    Default port

    Points: 1485

    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

    SSC Enthusiast

    Points: 104

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply