> I need to join two tables, the issue is that in one table the foreign > key is a four part compound key. In the other table, the foreign key is > a single varchar field that concatenates the four parts into a single > field. A final twist is that the forth part is sometimes a value and > sometimes not, in which case the first value is repeated. > > Here is an attempt to draw this out. > > PRODUCT > mfg_firmno - number(7) > label_seq_no - number(5) > revision_no - char(2) > reg_firmno - number(7) > > PUR > epa_registration_num - varchar(21)
How about creating a view for PRODUCT that combines those fields into a single column, using the rules you outlined, to then join to PUR.epa_registration_num? Hopefully that would simplify the join. Phillip ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3051 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
