Having problems with message search?
I found a solution that is really quite simple; unless those who are more sql experienced can find an issue with it... All the pieces where in Google but it took me some time to piece it together so I thought I would post it. Also I am (for now) just sorting on one item but you can see how easily it can be expanded.
TABLE-1 PK = FEILD_B
TABLE-2 PK = FEILD_B and FEILD_A (Which is the PK for TABLE-5)
TABLE-3 FK = FEILD_B (PK = FEILD_E and contains FEILD_D which is a FK in TABLE-6)
TABLE-4 FK = FEILD_B
TABLE-2 PK = FEILD_B and FEILD_A (Which is the PK for TABLE-5)
TABLE-3 FK = FEILD_B (PK = FEILD_E and contains FEILD_D which is a FK in TABLE-6)
TABLE-4 FK = FEILD_B
FEILD_A = the value of a PK in TABLE-7, in this case I want it to be 5
INSERT INTO TABLE-2(FEILD_A, FEILD_B, FEILD_C)
WITH TEMP AS (
SELECT
Distinct 5 as FEILD_A, 'Y' as FEILD_C, t1.FEILD_B, t4.SID
FROM
(SELECT FEILD_B, MAX(SIGNIN_ID) AS SID FROM TABLE-4 GROUP BY FEILD_B) t4
INNER JOIN TABLE-1 t1
ON t4.FEILD_B = t1.FEILD_B
INNER JOIN TABLE-3 t3
ON t1.FEILD_B = t3.FEILD_B
WHERE
t1.FEILD_F = 'Male' and
0 < (SELECT count(tt3.FEILD_B) FROM TABLE-3 tt3 WHERE tt3.FEILD_B = t3.FEILD_B and tt3.FEILD_D = 1 and tt3.FEILD_E = 3) and
t1.FEILD_B NOT IN (SELECT FEILD_B from TABLE-2 where FEILD_A = 5)
ORDER BY t4.SID DESC)
SELECT FEILD_A, FEILD_B, FEILD_C FROM TEMP
WITH TEMP AS (
SELECT
Distinct 5 as FEILD_A, 'Y' as FEILD_C, t1.FEILD_B, t4.SID
FROM
(SELECT FEILD_B, MAX(SIGNIN_ID) AS SID FROM TABLE-4 GROUP BY FEILD_B) t4
INNER JOIN TABLE-1 t1
ON t4.FEILD_B = t1.FEILD_B
INNER JOIN TABLE-3 t3
ON t1.FEILD_B = t3.FEILD_B
WHERE
t1.FEILD_F = 'Male' and
0 < (SELECT count(tt3.FEILD_B) FROM TABLE-3 tt3 WHERE tt3.FEILD_B = t3.FEILD_B and tt3.FEILD_D = 1 and tt3.FEILD_E = 3) and
t1.FEILD_B NOT IN (SELECT FEILD_B from TABLE-2 where FEILD_A = 5)
ORDER BY t4.SID DESC)
SELECT FEILD_A, FEILD_B, FEILD_C FROM TEMP
From: Bryan O'Neal
Sent: Saturday, June 14, 2008 1:01 PM
Subject: SQL Question - Complex ordering
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)
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)
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?
Offline 
Send Email