SQL Question - Complex ordering

[Bryan O'Neal]

Ok, I have TABLE-1, TABLE-2, TABLE-3, TABLE-4,TABLE-5 involved
TABLE-1 is a 1 to many on TABLE-2
TABLE-1 is a 1 to many on TABLE-3
TABLE-1 is a 1 to many on TABLE-4
TABLE-2 has a primary key of (TABLE-1_ID, TABLE-3_ID)
 
I want to insert records into TABLE-2 based on information in TABLE-1
and TABLE-4 but order the insert by values information in table TABLE-2
and TABLE-5
 
 
5 is the value of TABLE-3 ID I want to insert with whatever TABLE-1 id's
comeback from the query of TABLE-1 and TABLE-4 with a toggle of 'Y' in
the toggle field.
 
 

INSERT INTO TABLE-2( TABLE-3_ID, TABLE-1_ID, TOGGLEFIELD)
SELECT DISTINCT 5 as TABLE-3_ID, T1.TABLE-1_ID, 'Y' as TOGGLEFIELD FROM
TABLE-1 t1, TABLE-4 t4,  TABLE-5 t5
WHERE t4.TABLE-4_ID = t1.TABLE-4_ID and t1.SOME_FIELD = 'value'
and 0 < (SELECT count(td4.TABLE-4_ID) FROM TABLE-4 td4 WHERE
td4.TABLE-4_ID = t4.TABLE-4_ID and t4.A_FIELD = 1 and td4.B_FIELD = 3)
and 0 < (SELECT count(td4.TABLE-4_ID) FROM TABLE-4 td4 WHERE
td4.TABLE-4_ID = t4.TABLE-4_ID and t4.A_FIELD = 10 and td4.B_FIELD = 83)

and t1.TABLE-1_ID NOT IN (SELECT TABLE-1_ID from TABLE-2 where
TABLE-3_ID = 5) 
 
Alternately
 
INSERT INTO TABLE-2( TABLE-3_ID, TABLE-1_ID, TOGGLEFIELD)
SELECT 5 as TABLE-3_ID, T1.TABLE-1_ID, 'Y' as TOGGLEFIELD FROM TABLE-1
t1, TABLE-4 t4,  TABLE-5 t5
WHERE t4.TABLE-4_ID = t1.TABLE-4_ID and t1.SOME_FIELD = 'value'
and 0 < (SELECT count(td4.TABLE-4_ID) FROM TABLE-4 td4 WHERE
td4.TABLE-4_ID = t4.TABLE-4_ID and t4.A_FIELD = 1 and td4.B_FIELD = 3)
and 0 < (SELECT count(td4.TABLE-4_ID) FROM TABLE-4 td4 WHERE
td4.TABLE-4_ID = t4.TABLE-4_ID and t4.A_FIELD = 10 and td4.B_FIELD = 83)

and t1.TABLE-1_ID NOT IN (SELECT TABLE-1_ID from TABLE-2 where
TABLE-3_ID = 5) 
GROUP BY t1.TABLE-4_ID
 
gives the same results
 
However, I can not do any of the following because I get duplicate
TABLE-1_ID values with something like 
 
INSERT INTO TABLE-2( TABLE-3_ID, TABLE-1_ID, TOGGLEFIELD)
SELECT 5 as TABLE-3_ID, T1.TABLE-1_ID, 'Y' as TOGGLEFIELD FROM TABLE-1
t1, TABLE-4 t4,  TABLE-5 t5
WHERE t4.TABLE-4_ID = t1.TABLE-4_ID and t1.SOME_FIELD = 'value'
and 0 < (SELECT count(td4.TABLE-4_ID) FROM TABLE-4 td4 WHERE
td4.TABLE-4_ID = t4.TABLE-4_ID and t4.A_FIELD = 1 and td4.B_FIELD = 3)
and 0 < (SELECT count(td4.TABLE-4_ID) FROM TABLE-4 td4 WHERE
td4.TABLE-4_ID = t4.TABLE-4_ID and t4.A_FIELD = 10 and td4.B_FIELD = 83)

and t1.TABLE-1_ID NOT IN (SELECT TABLE-1_ID from TABLE-2 where
TABLE-3_ID = 5) 
GROUP BY t1.TABLE-4_ID, t5.TABLE-5_ID
 
 
Any suggestions?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20080614/46f93089/attachment.htm