locked
auto populate column via lookup RRS feed

  • Question

  • Hi I have a "Challenge" with regard to some database design and hoping this could be the solution

     

    I have two tables:

     

    Table one contains a code column and a description column. Table two contains a load of columns plus a code column and a description column.

     

    Is there a way to get the table 2 description column to populate from table one based on the code matching the code from each table?    


    Tuesday, July 28, 2009 11:05 PM

Answers

  • You could, but why would you want to?  In a normalized design, you would JOIN the two tables together when you query the data on the code column so that your output could contain the description.  If you'd like an example query for how to do that consider:

    use tempdb
    go
    create table table1(code int identity primary key, description varchar(40))
    create table table2(rowid int identity primary key, code int, othercolumn varchar(40))
    go
    insert into table1 values ('some description1')
    insert into table1 values ('some description2')
    insert into table2 values (1, 'some other data')
    insert into table2 values (1, 'some other data again')
    insert into table2 values (2, 'some more other data')
    go
    select * from table1;
    select * from table2;
    select table2.rowid, table1.description, table2.othercolumn
    from table1
    join table2 on table1.code = table2.code
    go
    drop table table1
    drop table table2


    This is a fundamental part of relational database design.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by Elroacho Wednesday, July 29, 2009 3:57 AM
    Wednesday, July 29, 2009 3:51 AM