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]