Clay,

You haven't explained very well exactly what you are after, which is
perhaps why the vague response you have so far received. I had to
guess from your queries your schema, so you may have to adjust this
procedure if I have guessed wrong.

Input tables:

Dept
(
DEPTNO
)



EmpInfo
(
ID,
HOMEDEPT,
HOMENC
)

NATCL
(
NCNO
)

And the table you want is:

deptno_ncno
(
DEPTNO,
NCNO
)

This script should do it in iSQL etc. It works by creating a stored
procedure that cycles through the tables and runs the insert queries.
Now, this will populate it once, if you need it to remain up-to-date,
you will need to manage that some other way, such as through triggers.



set term ^;

create procedure tmp_fill_deptno_ncno
as
declare variable vdeptno integer;
declare variable vhomenc integer;
begin
for select deptno from dept into :vdeptno do
begin
-- cycle through departments

for select distinct(homenc) from empinfo where homedept
= :vdeptno into :vhomenc do
begin
-- cycle through empinfo records for current department
insert into deptno_ncno (deptno, ncno) values
(:vdeptno, :vhomenc);
end
end
end
^

commit work
^

execute procedure tmp_fill_deptno_ncno
^

commit work
^

drop procedure tmp_fill_deptno_ncno
^

commit work
^


Adam









--- In firebird-support@yahoogroups.com, "Clay Shannon"
<cshannon@d...> wrote:

> I created a new many-to-many table that holds two columns: DEPTNO,

and NCNO

>
>
>
> For every DeptNo, there are 1..N NCNO values, e.g.:
>
>
>
> DEPTNO NCNO
>
> 6010 110
>
> 6010 115
>
> 6010 120
>
> 6011 150
>
> Etc.
>
>
>
> And that's how I want my new table to be popul8d (as above).
>
>
>
> As of now, there is a DEPT table with all Dept Nos, and a NATCL

table with

> all the NCNOs, and an EmpInfo table, in which each record holds the

DEPT a

> person works in and which NCNO (job title) they hold. So from the

EmpInfo

> table, I can create this N..N table (I know I can, I just don't

know how).

>
>
>
> I could do it in Delphi using string lists and loops and queries,

but I'm

> sure there's a faster way using SQL.
>
>
>
> Something like the following doesn't work but might give the idea

of what

> I'm trying to accomplish:
>
>
>
> insert into deptno_ncno (deptno, ncno)
>
> values (6010,
>
> (select distinct(homenc)
>
> from empinfo
>
> where homedept = 6010))
>
>
>
> --even if the above worked (which it doesn't because the select

selects

> multiple records), I would have to manually enter the deptno each

time. How

> can I populate the whole table with one SQL statement--is it

possible? I'm

> sure it must be.
>
>
>
> Clay Shannon,
>
> Dimension 4 Software
>
>
>
>
>
> [Non-text portions of this message have been removed]