SQL Question - Complex ordering

[Bryan O'Neal]

You should see what I did need to write in java.  For every record read
in 12 different objects are used, up to 4 different possible conditions
are checked for each (What conditions and how many depend on what object
and the data in that object), and over 40 fields get changed.  This is
with the recursive functions precalculated by the DB ;)
Of course it gets really complex with the fact that I do logic checks in
the ap to make sure my information is clean and makes sense :)  

-----Original Message-----
From: plug-discuss-bounces at lists.plug.phoenix.az.us
[mailto:plug-discuss-bounces at lists.plug.phoenix.az.us] On Behalf Of
Joseph Sinclair
Sent: Saturday, June 14, 2008 3:11 PM
To: Main PLUG discussion list
Subject: Re: SQL Question - Complex ordering

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 at 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 at lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss