Outer nested non-ANSI joins (aaaarrrrggg)

PhilSchmidt · #1 (**permalink**) 11-23-10, 09:38

I've tried everything and I just can't convert this to an ANSI join format and have it work.

SELECT
FIELD LIST
FROM
order o,
OUTER(tour t1, v_order_to_job_ids vo1, tour_point tp1, activity act1, OUTER activity_aux aa),
OUTER(tour t2, v_order_to_job_ids vo2, tour_point tp2, activity act2),
OUTER order_aux oa,
OUTER (
SELECT order_id,
SUM(percent_of_trailer) *100 AS percentOfTrailer,
first_delivery
FROM order_detail
WHERE order_id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
AND status_cid NOT IN(1214, 1215)
/* Inactive, Delete */

GROUP BY order_id, first_delivery) pot
WHERE act1.status_cid != 1001
AND act1.tour_point_id = tp1.id
AND act1.sub_type_cid != 1367
AND tp1.tour_id = t1.id
AND tp1.seq_num = 1
AND vo1.o_id = o.id
AND vo1.t_id = t1.id
AND act2.status_cid != 1001
AND act2.tour_point_id = tp2.id
AND act2.sub_type_cid != 1367
AND tp2.tour_id = t2.id
AND tp2.seq_num =(
SELECT max(seq_num)
FROM tour_point
WHERE tour_id = t2.id
AND status_cid != 1311)
AND vo2.o_id = o.id
AND vo2.t_id = t2.id
AND o.id = oa.order_id
AND o.id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
AND o.cust_role_id = 64
AND aa.activity_id = act1.id
AND aa.active = 1
AND o.id = pot.order_id

Here is how I THOUGHT it should look but it does not work . . .

SELECT
FIELD LIST
FROM
order o
LEFT OUTER JOIN v_order_to_job_ids vo1
ON vo1.o_id = o.id
INNER JOIN tour t1
ON vo1.t_id = t1.id
INNER JOIN tour_point tp1
ON (t1.id = tp1.tour_id and tp1.seq_num = 1)
INNER JOIN activity act1
ON (act1.tour_point_id = tp1.id AND act1.sub_type_cid != 1367 AND act1.status_cid != 1001)
LEFT OUTER JOIN activity_aux aa
ON (act1.id = aa.activity_id AND aa.active = 1)
LEFT OUTER JOIN v_order_to_job_ids vo2
ON o.id = vo2.o_id
INNER JOIN tour t2
ON vo2.t_id = t2.id
INNER JOIN tour_point tp2
ON (tp2.tour_id = t2.id AND tp2.seq_num =
(
SELECT max(seq_num)
FROM tour_point
WHERE tour_id = t2.id
AND status_cid != 1311))
INNER JOIN activity act2
ON (act2.tour_point_id = tp2.id AND act2.status_cid != 1001 AND act2.sub_type_cid != 1367)
LEFT OUTER JOIN order_aux oa
ON o.id = oa.order_id
LEFT OUTER JOIN
(
SELECT order_id,
SUM(percent_of_trailer) *100 AS percentOfTrailer,
first_delivery
FROM order_detail
WHERE order_id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
AND status_cid NOT IN(1214, 1215)
/* Inactive, Delete */

GROUP BY order_id, first_delivery
) pot
ON o.id = pot.order_id
WHERE
o.id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
AND o.cust_role_id = 64

PhilSchmidt · #2 (**permalink**) 11-23-10, 10:35

The query just runs forever . . .

SELECT
*
FROM
order o
left outer join
(tour t1
inner join v_order_to_job_ids vo1
ON vo1.t_id = t1.id
inner join tour_point tp1
ON tp1.tour_id = t1.id and tp1.seq_num = 1
inner join activity act1
ON act1.tour_point_id = tp1.id and act1.status_cid != 1001 and act1.sub_type_cid != 1367
left OUTER join activity_aux aa
ON act1.id = aa.activity_id and aa.active = 1)
ON o.id = vo1.o_id
left OUTER join
(tour t2
inner join v_order_to_job_ids vo2
ON vo2.t_id = t2.id
inner join tour_point tp2
ON (tp2.tour_id = t2.id AND tp2.seq_num =
(
SELECT max(seq_num)
FROM tour_point
WHERE tour_id = t2.id
AND status_cid != 1311
)
)
inner join activity act2
ON act2.tour_point_id = tp2.id AND act2.status_cid != 1001 AND act2.sub_type_cid != 1367)
ON o.id = vo2.o_id
left OUTER join order_aux oa
ON o.id = oa.order_id
left OUTER join (
SELECT order_id,
SUM(percent_of_trailer) *100 AS percentOfTrailer,
first_delivery
FROM order_detail
WHERE order_id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
AND status_cid NOT IN(1214, 1215)
/* Inactive, Delete */

GROUP BY order_id, first_delivery) pot
ON o.id = pot.order_id
WHERE
o.id IN(5852869, 5853283, 5853284, 5853295, 5853316, 5853318, 5853319, 5853320, 5853327, 5853633, 5853634, 5853645, 5854016, 5854493, 5854494, 5854495, 5857370, 5857371, 5857372, 5857373, 5858552, 5858553, 5858554, 5858555, 5858556, 5858557, 5858558, 5858559, 5858560, 5858561, 5858562, 5858563, 5858564, 5861248, 5861877, 5861878, 5861879, 5861881, 5861890, 5861891, 5861892)
AND o.cust_role_id = 64

Internet Services

Web Development

Digital Marketing

Consumer Tech