>I have two tables as follows:
>
>Table 1 has two fields, oecid and oecname. For example,
>
>MCOR1985..... 3M Corporation
>ABBL1991..... ABB Lummus
>BLAC2130..... Black & Veatch
>
>Table 2 has a multiple number of fields of which I am concerned with the following four: projectid, owner, engineer, and contractor. The owner, engineer, and contractor fields contain an 'oecid'.
>
>I want to be able to pull out the projectids whose owner,engineer or contractor 'oecid' matches an 'oecid' form Table 1. In addition, I would like to convert the 'oecid' in each of the owner, engineer, contractor fields to the corresponding 'oecname'.
>
>I hope this makes sense! Any suggestions would be greatly appreciated.
>
>Thanks in advance for your help,
>Russell Clendenon
Slightly different SQL, than Sergey's should do the job too (not tested):
select Table2.*, Table1.OecName from Table2 inner join table1 on Table2.Owner=Table1.oecID ;
union ;
select Table2.*, Table1.OecName from Table2 inner join table1 on Table2.Engineer=Table1.oecID ;
union ;
select Table2.*, Table1.OecName from Table2 inner join table1 on Table2.Contractor=Table1.oecID ;
into table EverybodyWithDescr
If it's not broken, fix it until it is.
My Blog