Re: SQL Question
Jeff So wrote:
>
> On 24 Jun 1997, 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?
> I have a similar problem, except that there is no fix
> number of record on the detail table which related to
> the master table. With ESQL/C's cursor function, I can
> do it. But some the web development tools I am using
> can only accept SQL statement or SPL. So, wondering
> is it possible to do this with just SQL or SPL?
Possible, maybe. But incredibly ugly and inefficient.
Here's an attempt assuming only one detail table
and at most 2 records for each master record:
select t1.id, t1.name, t1.city,
(select code from code_table t2
where t1.id=t2.id
and t2.rowid=
(select min(rowid) from code_table t3 where t3.id=t2.id)
) code_1,
(select code from code_table t4
where t1.id=t2.id
and t4.rowid >
(select min(rowid) from code_table t5 where t5.id=t4.id)
and t4.rowid <
(select min(rowid) from code_table t6
where t6.id=t4.id and t6.rowid > t4.rowid)
) code_2
from master_table t1
#include <all the usual comments about not
recommending use of rowids>
Yuck! and I'm not even sure if it works.
4GL or at least ESQL/C is the way to go
on this one.
Good Luck,
Douglas Wilson