reference from one table to another

  • Jeff Maahs

    Old Hand

    Points: 374

    Hi,

    I have two tables as below.

    mls table

    id(int), photo(varchar)

    mls_photo table

    id(int), medium_photo(image),medium_photo2(image)...,medium_photo9(image)

    The mls.photo column is filled by the app when photos are added to the photo db.

    I would like mls.photo calculated instead so that any of the columns that are Not NULL will cause a corresponding number to be concatenated to mls.photo.

    For example:mls.id = 234567, and mls_photo.medium_photo,mls_photo.medium_photo2 are the only ones with info, then mls.photo should = '12'.

    If mls_photo.medium_photo3 is added later, the mls.photo would now be '123'.

    Here is a script I run periodically to make sure the field is reflecting correctly. This is really tough to run on the customers system since we are talking 50000 records so I would like to change to a calculated field if possible.

    update mls set photo=0

    go

    update mls set photo='1' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo is not null)

    go

    update mls set photo=photo + '2' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo2 is not null)

    go

    update mls set photo=photo + '3' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo3 is not null)

    go

    update mls set photo=photo + '4' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo4 is not null)

    go

    update mls set photo=photo + '5' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo5 is not null)

    go

    update mls set photo=photo + '6' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo6 is not null)

    go

    update mls set photo=photo + '7' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo7 is not null)

    go

    update mls set photo=photo + '8' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo8 is not null)

    go

    update mls set photo=photo + '9' where id in (select id from [mlshuphoto].[dbo].[mls_photo] where medium_photo9 is not null)

    go

    update mls set photo=replace (photo,'0',NULL) where photo = '0'

    Thanks for you help!

  • Paul Ibison

    SSCertifiable

    Points: 5303

    Just about the most convoluted statement I've producted, but will product the required string in one line, which is the required aim, I believe.

    select isnull(nullif('1',isnull(medium_photo,'1')),'') +

    isnull(nullif('2',isnull(medium_photo2,'2')),'') + ....etc

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • nigelrivett

    SSCertifiable

    Points: 5362

    You could do this with a bit map

    1 + 2 + 4 + 8 + ...

    Probably more convoluted but

    update mls

    set photo = coalesce(left('1' + char(convert(binary(8),medium_photo1)),1),'')

    + coalesce(left('2' + char(convert(binary(8),medium_photo2)),1),'')

    +

    from [mlshuphoto].[dbo].[mls_photo] ph

    where mls.id = ph.id

    It is probably more readable to

    case when medium_photo1 is null then '' else '1' end

    + case when medium_photo2 is null then '' else '2' end

    + ...


    Cursors never.
    DTS - only when needed and never to control.

  • Jeff Maahs

    Old Hand

    Points: 374

    Hi Paul,

    That's absolutely disgusting. It'll take me hours just to figure out! I love functions like this one! Hope your brain stops hurting soon...

    That will be great for fixing the field, but I cant use this as a computed field because of the subquery. I was hoping for a computed field in the mls table to reference the mls_photo table. If I cant, then how can I change this to be a computed field in the mls_photo table?

    Thank you!

  • Jeff Maahs

    Old Hand

    Points: 374

    Hi nigelrivett,

    I used your case statement and created a computed field in the mls_photo table. It works great.

    But I would like to add a calc field to my mls table. This is what I have but it doesnt work:

    alter table mls add photoc AS (

    case when vreb_photo.mls_photo.medium_photo is null then '' else '1' end

    + case when vreb_photo.mls_photo.medium_photo2 is null then '' else '2' end

    + case when vreb_photo.mls_photo.medium_photo3 is null then '' else '3' end

    + case when vreb_photo.mls_photo.medium_photo4 is null then '' else '4' end

    + case when vreb_photo.mls_photo.medium_photo5 is null then '' else '5' end

    + case when vreb_photo.mls_photo.medium_photo6 is null then '' else '6' end

    + case when vreb_photo.mls_photo.medium_photo7 is null then '' else '7' end

    + case when vreb_photo.mls_photo.medium_photo8 is null then '' else '8' end

    + case when vreb_photo.mls_photo.medium_photo9 is null then '' else '9' end)

    I get the error:"Invalid column 'medium_photo' specified in constraint definition."

    Thanks!

  • nigelrivett

    SSCertifiable

    Points: 5362

    Try adding a trigger to the vreb_photo table which updates mls..photoc field.

    Note in your defintion you are not saying which row in vreb_photo to get the values from which should be a hint that it isn't going to work.

    Edited by - nigelrivett on 11/09/2001 08:52:40 AM


    Cursors never.
    DTS - only when needed and never to control.

  • Jeff Maahs

    Old Hand

    Points: 374

    vreb_photo is the database,

    mls_photo is the table,

    medium_photo is the column.

    The 2 tables have an [id] column in common.

    Could I get some help on a trigger? This is my first attempt (as you can see).

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR DELETE , INSERT , UPDATE

    AS vreb.mls.photoc = mls_photo.dbo.photoc, vreb.mls.p_mod_date_time = getdate() where mls_photo.id = vreb.mls.id

    IF COLUMNS_UPDATED() > 0

  • Paul Ibison

    SSCertifiable

    Points: 5303

    Phred,

    my solution is not so bad as it seems. Ignore the Select - that was for illustration - this code can be used as a computed column. However, admittedly the case statement is (a lot!) more clear.

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • Jeff Maahs

    Old Hand

    Points: 374

    Paul,

    Dont get me wrong. I thought your solution was great - intricate but great.

    alter table mls add [photoc] AS (

    isnull(nullif('1',isnull([vreb_photo].[mls_photo].[medium_photo],'1')),'') +

    isnull(nullif('2',isnull([vreb_photo].[mls_photo].[medium_photo2],'2')),'') +

    isnull(nullif('3',isnull([vreb_photo].[mls_photo].[medium_photo3],'3')),'') +

    isnull(nullif('4',isnull([vreb_photo].[mls_photo].[medium_photo4],'4')),'') +

    isnull(nullif('5',isnull([vreb_photo].[mls_photo].[medium_photo5],'5')),'') +

    isnull(nullif('6',isnull([vreb_photo].[mls_photo].[medium_photo6],'6')),'') +

    isnull(nullif('7',isnull([vreb_photo].[mls_photo].[medium_photo7],'7')),'') +

    isnull(nullif('8',isnull([vreb_photo].[mls_photo].[medium_photo8],'8')),'') +

    isnull(nullif('9',isnull([vreb_photo].[mls_photo].[medium_photo9],'9')),''))

    I get the same problem however. (Invalid column 'medium_photo' specified in constraint definition.)

    Is it possible to have a calculated field that references a column in another table?

  • Paul Ibison

    SSCertifiable

    Points: 5303

    Can you post up your table definitions - I can look at it later if it's still not solved by then.

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • Jeff Maahs

    Old Hand

    Points: 374

    Here are the table defs I inherited. The mls table is truncated down to 3 fields. I did not add the calculated field of course to the mls table yet. But if I have to go with the calc field on the mls_photo database and use a trigger, then this will be what has to be done. Obviously I'm a novice and I do appreciate everyones help - VERY much.

    CREATE TABLE [dbo].[mls] (

    [id] [int] NOT NULL Clusterd Unique,

    [p_mod_date_time] [smalldatetime] NULL ,

    [photo] [varchar] (11) NULL ,

    ) ON [PRIMARY]

    GO

    id and p_mod_date_time are indexed.

    CREATE TABLE [dbo].[mls_photo] (

    [id] [int] NOT NULL ,

    [large_photo] [image] NULL ,

    [medium_photo] [image] NULL ,

    [small_photo] [image] NULL ,

    [medium_photo2] [image] NULL ,

    [medium_photo3] [image] NULL ,

    [medium_photo4] [image] NULL ,

    [medium_photo5] [image] NULL ,

    [medium_photo6] [image] NULL ,

    [medium_photo7] [image] NULL ,

    [medium_photo8] [image] NULL ,

    [medium_photo9] [image] NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    large_photo and small_photo are only used on the central database, not in client machines.

  • Paul Ibison

    SSCertifiable

    Points: 5303

    phred,

    this code below works ok and is a shortened version to do what you want. However, it has dbo as owner of the tables, so I think the problem may be one of ownership of tables - in your case mls_photo is the table owner in the alter table statement, but the script for the tables is dbo - is mls_photo the dbo? If not that could explain it. If they are the same user, then try my code above and if this works on your system it can be extended with the other columns.

    CREATE TABLE mls_photo

    (

    id int NOT NULL ,

    large_photo image NULL ,

    medium_photo image NULL ,

    small_photo image NULL

    )

    alter table mls_photo add xxx as

    (case when medium_photo is null then '' else '1' end)

    + (case when small_photo is null then '' else '1' end)

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • Jeff Maahs

    Old Hand

    Points: 374

    Thanks Paul, that does work fine.

    I was wondering though, if it is possible to put this calculated field into the mls table and reference the mls_photo table from there? Can you do a cross table calculated field?

    If not, one of the last posts shows a trigger (my very first) that would trigger when ANY of the mls_photo fields are inserted, updated, deleted. Do I have this right?

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR DELETE , INSERT , UPDATE

    AS vreb.mls.photoc = mls_photo.dbo.photoc, vreb.mls.p_mod_date_time = getdate() where mls_photo.id = vreb.mls.id

    IF COLUMNS_UPDATED() > 0

    Thanks again!

  • Paul Ibison

    SSCertifiable

    Points: 5303

    phred,

    here's an insert/update trigger for you - (will leave you to create the corresponding delete one!)

    CREATE TRIGGER photo_numbers

    ON mls_photo

    FOR INSERT , UPDATE

    as

    declare @PhotoState varchar(20)

    set @PhotoState = case when inserted.medium_photo is null then '' else '1' end

    + case when inserted.medium_photo2 is null then '' else '2' end

    + case when inserted.medium_photo3 is null then '' else '3' end

    + case when inserted.medium_photo4 is null then '' else '4' end

    + case when inserted.medium_photo5 is null then '' else '5' end

    + case when inserted.medium_photo6 is null then '' else '6' end

    + case when inserted.medium_photo7 is null then '' else '7' end

    + case when inserted.medium_photo8 is null then '' else '8' end

    + case when inserted.medium_photo9 is null then '' else '9' end

    insert into mls(p_mod_date_time, photo)

    values getdate(), @PhotoState

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • Jeff Maahs

    Old Hand

    Points: 374

    That is awesome Paul! Thank you for all the help. And for nigelrivett's help as well.

    I didn't get any response at the Microsoft SQL forum. I think I'll stick around here and see if I can help anyone in return...Thanks again!

Viewing 15 posts - 1 through 15 (of 29 total)

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