I'd ask why you need this complex of a data model, but I'm afraid of the answer....
With this many complex and overlapping conditions, you're almost certainly better off performing the desired transformation in a separate program (written in Java, Python, or something similar).
You'd read the source records with a relatively simple query (or a small number of queries) and, walking through the results, calculate the outputs and add them to a small set of bulk insert or update statements.
Once all the inputs are read and processed, you'd commit the insert/update statements and call it a day.
Just my thoughts...
Bryan O'Neal wrote:
> 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?
>
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
---------------------------------------------------
PLUG-discuss mailing list -
PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)