Post reply

relationships

  • November 26, 2003 at 6:54 am

    #176953

    Hi,

    I am having a problem with creating relationships between tables in SQL Server. I imported the db from access, and there are a lot of 'orphans' between tables, and consequently any time I try to create PK and FK constraints there are errors. What would be the most efficient query for finding the values that are not present in the parent table, that are present in the child table?

    Thanks for any help

  • November 26, 2003 at 6:57 am

    #483722

    Take a look at BOL for joins-SQL Server ->outer -> Using outer Joins.

    I bet there is also something here in the script section

    Frank

    http://www.insidesql.de

    http://www.familienzirkus.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 27, 2003 at 4:09 am

    #483884

    Hi,

    I had a look at outer joins but am still having problems sorting out the relationships problem. I am setting the values in one table to 'VAL', but cannot seem to get the actual statement working. It seems okay to me...

    update t1

    set field = 'VAL'

    where field not in (select t2.field from t2 right outer join t1

    ON t2.field = t1.field )

    Basically, all I am trying to do is set all the values to 'VAL' in the foriegn key table wherever the value in there already does not occur in the primary key table... but it is not working and I am not sure why. Can anyone give me any hints?

  • November 27, 2003 at 4:30 am

    #483892

    I think what's wrong is that the nested select will return NULL for any rows in t1 where field doesn't appear in t2. This is because you are selecting t2.field in your nested select.

    Because your condition is simply based on the existence of a value in t2, you can do the update without a join as follows:

    

    update t1

    set field = 'VAL'

    where field not in (select field from t2 )

    Just for the sake of interest, if you need to use an outer join for such an update, you would need something like the following:

    

    update t1

    set field = 'VAL'

    from t1 left outer join t2 on t1.field = t2.field

    where t2.field is null  

    -- i.e. where t2.field does *not* exist for t1.field

    Cheers,

    mia

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.


    Cheers,
    mia

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

  • November 27, 2003 at 4:38 am

    #483893

    Thanks Mia, that worked perfectly and helped me understand where I was going wrong.

Viewing 5 posts - 1 through 4 (of 4 total)

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