Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2013

    Post Unanswered: Help with a left join subselect

    My current statement returns order header, order item and independent routing information. The problem is my left join returns more than one row from the indie_routing table.

    I need it to return only one row (the row with the latest i_ts (datetime)) value. Is this something anyone could help me out with?

    I'm pretty sure I need to do a subselect on my join with a top 1 ordered by i_ts desc but i just cant seem to get the syntax correct.

    I would be eternally grateful if anyone could help me out as im going nuts trying to figure it out!

    SELECT ord_head.a_ref, ord_head.a_ts, ord_head.a_status, ord_head.a_account, ord_head.a_sub_account,
    ord_head.a_cust_ord_ref, ord_head.a_disc_rate, ord_head.a_tot_flower_val, ord_head.a_tot_relay_chgs,
    ord_head.a_tot_disc, ord_head.a_total, ord_head.a_pay_method,

    ord_item.b_item_num, ord_item.b_prod,
    ord_item.b_status, ord_item.b_eflorist_ref, ord_item.b_extra_cost1, ord_item.b_unit_cost, ord_item.b_qty,
    ord_item.b_flower_val, ord_item.b_relay_chg, ord_item.b_sc_type, ord_item.b_disc, ord_item.b_disc_rate,
    ord_item.b_total, ord_item.b_del_date, ord_item.b_occ, ord_item.b_country, ord_item.b_sd_member,
    ord_item.b_ex_member, ord_item.b_relay_value, ord_item.b_ex_del, ord_item.b_routed_status,
    ord_item.b_routed_ts, ord_item.b_routed_by, ord_item.b_pcode,

    indie_routing.i_shop_id, indie_routing.i_ts,
    indie_routing.i_shop_name, indie_routing.i_city, indie_routing.i_phone, indie_routing.i_payment_type,
    indie_routing.i_reason, indie_routing.i_contact_name

    FROM ord_head
    INNER JOIN ord_item
    ON ord_head.a_ref = ord_item.b_ref
    LEFT JOIN indie_routing
    ON ord_item.b_ref = indie_routing.i_ref
    AND ord_item.b_item_num = indie_routing.i_item
    WHERE ( ord_head.a_ts > (TODAY - 7 UNITs DAY))

    Order by a_ref

    O yea and i forgot to mention this is running a REALLY old version of informix!
    Last edited by JonBoy123; 01-25-13 at 11:36. Reason: additional info

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts