Update Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Update Help

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-19 : 10:54:07

Jeff writes "I need to add a key to a table. I have added the field, but I need to ensure the key I add is correct relationally.

I thought a subquery would be the best approach but I cannot find a sample that meets my needs.

TABLE-1
COMPANY_ID (PK)
COMPANY_NM

TABLE-2
ADDRESS_ID (PK)
COMPANY_ID (FK)
STR_1

TABLE-3 (ORIGINAL STRUCTURE)
STATE_ID (PK)
ADDRESS_ID (FK)
STATE_NM

TABLE-3a (NEW APPROACH)
STATE_ID (PK)
ADDRESS_ID (FK)
COMPANY_ID (FK)

I need to join across all three tables, adding COMPANY_ID to TABLE-3a ensuring that the ADDRESS_ID is consistent and the correct COMPANY_ID is selected. This is not an actual design, it represents the problem accurately.

Thanks."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 12:32:36

this can be a start for you

select t1.company_nm, t2.str_1, t3.state_nm, t4.company_id
from table1 as t1
left join table2 as t2 on t2.company_id = t1.company_id
left join table3 as t3 on t3.address_id = t2.address_id
left join table4 as t4 on t4.address_id = t2.address_id

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources