CASE in FK

[velocius]

i have the next problem

in the database we use, some of the data was imported from excel and excel made all numbers starting with a 0 lose that 0.

i have to find a way to link 20009 to 020009.

but the database has a rather poor design, so other records that still work can be

49 = 49
020010 = 020010
20011 = 20011

is there a way to, if a foreign key is not equal, switch to a diffrent one (i tried with cases and unions, but nothing worked), or would updating these lines be the best solution (theres about 50K lines in a database, and 5 databases, so updating would be alot of work)

also, this database will stop being used in march 2007, so it would be best if i could get it in a query now.

[almaz]

i have the next problem

in the database we use, some of the data was imported from excel and excel made all numbers starting with a 0 lose that 0.

i have to find a way to link 20009 to 020009.

but the database has a rather poor design, so other records that still work can be

49 = 49
020010 = 020010
20011 = 20011

is there a way to, if a foreign key is not equal, switch to a diffrent one (i tried with cases and unions, but nothing worked), or would updating these lines be the best solution (theres about 50K lines in a database, and 5 databases, so updating would be alot of work)

also, this database will stop being used in march 2007, so it would be best if i could get it in a query now.

It looks like your DB has a VERY BAD design.
Make sure that all your numeric fields are NUMERIC indeed, meaning that field that has a '020009' value has an INT datatype, not varchar/char/etc. When you do that you'll find that 020009 EQUALS TO 20009. Strange. isn't it? :). And all foreign keys would automagically work as expected.

[velocius]

It looks like your DB has a VERY BAD design.
Make sure that all your numeric fields are NUMERIC indeed, meaning that field that has a '020009' value has an INT datatype, not varchar/char/etc. When you do that you'll find that 020009 EQUALS TO 20009. Strange. isn't it? :). And all foreign keys would automagically work as expected.

that would eb a solution, if it werent for some records to be having L25482 as id

and i think L25482 won't fit in a int datatype :S

[almaz]

that would eb a solution, if it werent for some records to be having L25482 as id

and i think L25482 won't fit in a int datatype :S

And that means that you do have a bad DB design :\. In this case, if you cannot get rid of varchar data type as a primary key, you'll have to manually parse the column revealing "missing" primary keys, something like this one:

-- See if you have missing foreign key references.
select parent_id from child_table
where parent_id not in (select id from parent_table)
 
--Try to add zeros (only if it solves the issue):
 
update child_table
set parent_id = '0' + parent_id
where parent_id not in (select id from parent_table)
and ('0' + parent_id) in (select id from parent_table)
 

But still the best choice would be to review the DB design

[iburyak]

Do you have one to one relationship in those tables or one to many?

If one to one I think it is possilbe to write a query.

[velocius]

And that means that you do have a bad DB design :\. In this case, if you cannot get rid of varchar data type as a primary key, you'll have to manually parse the column revealing "missing" primary keys, something like this one:

-- See if you have missing foreign key references.
select parent_id from child_table
where parent_id not in (select id from parent_table)
 
--Try to add zeros (only if it solves the issue):
 
update child_table
set parent_id = '0' + parent_id
where parent_id not in (select id from parent_table)
and ('0' + parent_id) in (select id from parent_table)
 

But still the best choice would be to review the DB design

the not in allowed me to find out that one of the two references had only a few broken links. this solved the issue

the database is getting renewed, but we need to get data out of this one first, before going to the new one :)

thank you for the help