Joining tables

[crmayer]

I am not sure I can explain this, so I will try my best.

I have 3 tables, orders, movements and movement_order,
movement_order ties an order to multiple movements. Let's call one movement loaded and one empty.
movement_order fields:
id, order_id, movement_id
1, 100, 200
2, 100, 201
3, 101, 202
4, 101, 203

I want to search for an order, and get both the loaded and empty distance for that order. The "distance" is stored in the movement table.
I know that I have to link orders -> movement_order -> movement to get details about an move for an order. I have wrote a query that does this for me. The problem I run into is I am not sure how to do this "again" to get the the other information I need, the information about the 2nd move.
Prefered results:
order_id, loaded distance, empty distance
100, 250, 25

I guess my question is, how do I do this so I can return both the loaded move distance and empty move distance in one record?

As I said, I am sure this is not explained very well, I know what I want to do, just hard to explain it.

Please let me know if there is any more information that might be helpful here.

 

[drtree]

Hi,

I think you'd want to set up one main query with two subqueries pulling the 'empty' movement and 'loaded' movement.

So...

SELECT m.Order, l.Loaded, e.Empty
FROM orders m 
  LEFT OUTER JOIN (SELECT Order, Distance As Loaded
                   FROM Movements
                   WHERE MovementType = 'loaded') l
  ON m.Order = l.Order
  LEFT OUTER JOIN (SELECT Order, Distance As Empty
                   FROM Movements
                   WHERE MovementType = 'empty') e
  ON m.Order = e.Order

Basically, you'd just be querying the movement table twice - once for each specific movement type you want. You could store the information in temp tables too, which may be a little faster (you'd need to test this).

Hope that helps....

Doc Tree

 

[crmayer]

I think I see what you are saying, but it looks like the code you included is going off the assumption that the movement table has the order number in it, and it does not. The orders table and movement table are linked with a table called movement_order.

So, the m.order = l.order would not work. I am guessing I would need another join in there somehow no?

 

[crmayer]

I might be way off base here, but this is what I have started putting together. I am not very fimilar with nested joins i guess:

select empty_order.id order_no, empty_move.move_distance empty_miles, loaded_move.move_distance loaded_miles
from orders empty_order LEFT OUTER JOIN movement_order empty_movement_order LEFT OUTER JOIN movement empty_move on empty_move.id = empty_movement_order.movement_id and empty_move.loaded = 'E' on empty_movement_order.order_id = empty_order.id and empty_order.id = '0330367',
orders loaded_order LEFT OUTER JOIN movement_order loaded_movement_order LEFT OUTER JOIN movement loaded_move on loaded_move.id = loaded_movement_order.movement_id and loaded_move.loaded = 'L' on loaded_movement_order.order_id = loaded_order.id and loaded_order.id = '0330367'

Sorry if that is messy.

Any help as to if I am on the right track? Or way off base?

 

[crmayer]

OK, I know have another problem to throw in to this mix.
I was messing with this and got something very close, as a matter of fact I thought it was what I needed, but the numbers did not match the old reports numbers, so I got to digging deeper and I came to find out that there could be 2 empty moves per load, therefore we need to "sum" them.....

I am going to attach the query that I was close with, and name it Close

select  ship.zone_id zone_id,
count (*) loads, 
sum(movement.move_distance)empty_miles,
sum(bill_distance) miles, 
avg(bill_distance) avg_miles, 
sum(orders.freight_charge) line_haul_rev



from orders LEFT OUTER JOIN movement_order LEFT OUTER JOIN movement on movement.id = movement_order.movement_id and movement.loaded = 'E'and movement.company_id = 'TMS' and movement_order.company_id = 'TMS' on movement_order.order_id = orders.id and movement_order.company_id = 'TMS' and orders.company_id = 'TMS', 
stop ship, stop dest

where orders.shipper_stop_id = ship.id
and orders.consignee_stop_id = dest.id
and orders.revenue_code_id = '10'
and orders.status = 'D'
and orders.equipment_type_id = 'FLAT'
and dest.actual_arrival between '12/01/2009' and '12/31/2009'
and orders.company_id = 'TMS'
and ship.company_id = 'TMS'
and dest.company_id = 'TMS'
and movement.company_id = 'TMS'
and movement_order.company_id = 'TMS'

group by ship.zone_id

order by ship.zone_id

This is the query from the report writer (cognos impromptu) that I am trying to rewrite since we are losing it.

select T5."actual_departure", case  when T2."loaded" = 'E' then T2."move_distance" else NULL end 
 from "dbo"."stop" T5 LEFT OUTER JOIN ("dbo"."stop" T4 LEFT OUTER JOIN ((("dbo"."orders" T1 LEFT OUTER JOIN "dbo"."movement_order" T6 on T1."id" = T6."order_id") LEFT OUTER JOIN "dbo"."movement" T2 on T6."movement_id" = T2."id") LEFT OUTER JOIN "stop" T3 on T1."consignee_stop_id" = T3."id" and T1."shipper_stop_id" = T3."id") on T4."id" = T1."shipper_stop_id") on T5."id" = T1."consignee_stop_id"
 where T2."status" = 'D' and T1."company_id" = 'TMS ' and T1."equipment_type_id" = 'FLAT' and T1."revenue_code_id" = '10'

select T5."actual_departure", T1."id", T4."zone_id", {fn MONTH(T5."actual_departure")}, T1."company_id", T4."actual_departure", T1."total_charge", T1."bill_distance", T1."freight_charge", case  when T4."zone_id" = '1' then 'Jim' when T4."zone_id" = '2' then 'Jackie' when T4."zone_id" = '3' then 'Larry' when T4."zone_id" = '4' then 'Jan' when T4."zone_id" = '5' then 'Erin' else NULL end , CAST(T1."freight_charge" AS FLOAT) / (T1."bill_distance" + .00000001), T3."actual_departure", T2."move_distance", T1."rate", T1."extra_deliveries" + T1."extra_pickups", case  when T2."loaded" = 'E' then T2."move_distance" else NULL end , T1."status", T1."otherchargetotal", T1."equipment_type_id", T1."extra_pickups", T1."extra_deliveries", T2."loaded", T2."fuel_distance"
 from "dbo"."stop" T5 LEFT OUTER JOIN ("dbo"."stop" T4 LEFT OUTER JOIN ((("dbo"."orders" T1 LEFT OUTER JOIN "dbo"."movement_order" T6 on T1."id" = T6."order_id") LEFT OUTER JOIN "dbo"."movement" T2 on T6."movement_id" = T2."id") LEFT OUTER JOIN "stop" T3 on T1."consignee_stop_id" = T3."id" and T1."shipper_stop_id" = T3."id") on T4."id" = T1."shipper_stop_id") on T5."id" = T1."consignee_stop_id"
 where T2."status" = 'D' and T1."company_id" = 'TMS ' and T1."equipment_type_id" = 'FLAT' and T1."revenue_code_id" = '10'

select T5."actual_departure", {fn MONTH(T5."actual_departure")}, T4."zone_id", T1."id", case  when T2."loaded" = 'E' then T2."move_distance" else NULL end 
 from "dbo"."stop" T5 LEFT OUTER JOIN ("dbo"."stop" T4 LEFT OUTER JOIN ((("dbo"."orders" T1 LEFT OUTER JOIN "dbo"."movement_order" T6 on T1."id" = T6."order_id") LEFT OUTER JOIN "dbo"."movement" T2 on T6."movement_id" = T2."id") LEFT OUTER JOIN "stop" T3 on T1."consignee_stop_id" = T3."id" and T1."shipper_stop_id" = T3."id") on T4."id" = T1."shipper_stop_id") on T5."id" = T1."consignee_stop_id"
 where T2."status" = 'D' and T1."company_id" = 'TMS ' and T1."equipment_type_id" = 'FLAT' and T1."revenue_code_id" = '10'

select T5."actual_departure", {fn MONTH(T5."actual_departure")}, T4."zone_id", case  when T2."loaded" = 'E' then T2."move_distance" else NULL end 
 from "dbo"."stop" T5 LEFT OUTER JOIN ("dbo"."stop" T4 LEFT OUTER JOIN ((("dbo"."orders" T1 LEFT OUTER JOIN "dbo"."movement_order" T6 on T1."id" = T6."order_id") LEFT OUTER JOIN "dbo"."movement" T2 on T6."movement_id" = T2."id") LEFT OUTER JOIN "stop" T3 on T1."consignee_stop_id" = T3."id" and T1."shipper_stop_id" = T3."id") on T4."id" = T1."shipper_stop_id") on T5."id" = T1."consignee_stop_id"
 where T2."status" = 'D' and T1."company_id" = 'TMS ' and T1."equipment_type_id" = 'FLAT' and T1."revenue_code_id" = '10'

select T5."actual_departure", {fn MONTH(T5."actual_departure")}, T4."zone_id", T1."id"
 from "dbo"."stop" T5 LEFT OUTER JOIN ("dbo"."stop" T4 LEFT OUTER JOIN ((("dbo"."orders" T1 LEFT OUTER JOIN "dbo"."movement_order" T6 on T1."id" = T6."order_id") LEFT OUTER JOIN "dbo"."movement" T2 on T6."movement_id" = T2."id") LEFT OUTER JOIN "stop" T3 on T1."consignee_stop_id" = T3."id" and T1."shipper_stop_id" = T3."id") on T4."id" = T1."shipper_stop_id") on T5."id" = T1."consignee_stop_id"
 where T2."status" = 'D' and T1."company_id" = 'TMS ' and T1."equipment_type_id" = 'FLAT' and T1."revenue_code_id" = '10'

select T5."actual_departure", {fn MONTH(T5."actual_departure")}, case  when T2."loaded" = 'E' then T2."move_distance" else NULL end 
 from "dbo"."stop" T5 LEFT OUTER JOIN ("dbo"."stop" T4 LEFT OUTER JOIN ((("dbo"."orders" T1 LEFT OUTER JOIN "dbo"."movement_order" T6 on T1."id" = T6."order_id") LEFT OUTER JOIN "dbo"."movement" T2 on T6."movement_id" = T2."id") LEFT OUTER JOIN "stop" T3 on T1."consignee_stop_id" = T3."id" and T1."shipper_stop_id" = T3."id") on T4."id" = T1."shipper_stop_id") on T5."id" = T1."consignee_stop_id"
 where T2."status" = 'D' and T1."company_id" = 'TMS ' and T1."equipment_type_id" = 'FLAT' and T1."revenue_code_id" = '10'

I do applogize for the "mess", but that is how this report writer writes the code in the background, I know it is ugly and probably not even "proper"....

Any ideas?