Re: SQL Question
Jim Kist wrote:
>
> I have a set of tables, a master table and two detail tables. The detail
> tables have at most 5 records for each master record. I need to move these
> records to a flat file that has one record each set of master detail
> records. How do I go about doing this?
>
> Example:
> Master Table
> ===========
> Id
> Name
> City
>
> Detail Table 1
> ===========
> Id
> Code
>
> Detail Table 2
> ===========
> Id
> Date
>
> All tables are related by the 'Id' field. These records must go into the
> following table:
>
> Output
> ===========
> Id
> Name
> City
> Code_1
> Code_2
> Code_3
> Code_4
> Code_5
> Date_1
> Date_2
> Date_3
> Date_4
> Date_5
>
> Thanks for any help,
>
> Jim
Try the following. The "..." mean "iterate to 5".
create synonym code1 for code;
create synonym code2 for code;...
create synonym date1 for date;
create synonym date2 for date;...
select
id, name, city,
code.code,
code1.code code1,
...
date.date,
date1.date date1,
...
from
master_tbl,
outer code,
outer code1,
...
outer date,
outer date1
...
where
master_tbl.id = code.id and
master_tbl.id = code1.id and
...
master_tbl.id = date.id and
master_tbl.id = date1.id and
...
I think that will work. Lemme know.
--
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Blah Blah Lounge - "Do You Have Something Smart To Say?"
Cosmo Lee email: cosmo@echonyc.com Brooklyn, NY
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *