DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: two inner selects with a left join?

  1. #1
    Join Date
    May 2007
    Posts
    10

    two inner selects with a left join?

    I'm trying to take two selects and left join them.
    I originally had it as and outer select with a Left JOIN on the inner select. I however due to duplicate rows need to make my query into two inner selects
    to remove the duplicates, but still need to do a left join. Any ideas on how to structure this or if it's possible even?

    My two queries are verbatim of each other other than a variable change that looks like the following:

    Code:
    SELECT count(*) as total, min(soh.Anumber), min(soh.Onumber) as Onumber
     FROM dwasf,
      soh,
      cct,
      dis,
      act
     WHERE soh.sea = 'FL 05'
     AND soh.Ostatus = 'IN'
     AND soh.Onumber = dwasf.Sid
     AND dwasf.business_key = '2'
     AND soh.aAnumber = act.Anumber
     AND soh.d_date = cct.Cdate
     AND soh.dist = dis.id
     group by soh.Anumber
    order by count(*), soh.Anumber desc
    just how to Left join on two queries is eluding me....

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I've read your posting three times, got confused four times.

    Are you talking about:

    a- Left Joining two tables
    b- Set operators (you can always remove duplicates with a MINUS)
    c- All of the above
    d- None of the above
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2007
    Posts
    10
    I bascially need to take two queries and left join them, yes. The catch is that when doing so, my MIN does not work for the outer query to eliminate the duplicates.
    The data wont always be the same so I cannot set a condition to eliminate a duplicate as I think you mean in B.
    I clipped in the entire query which will show the two queries and the join.
    So, what I think needs to happen is the two queries need to be run as two different inner queries to eliminate the duplicates and then left joined somehow. This make sense?
    Something like:
    (Select ...
    ....)a
    (Select ...
    ...)b
    Left Join
    a on b

    Code:
    SELECT dwasf.saleid AS
    sale_id,
      act.account_number,
      act.account_name,
      soh.sheets_sent,
      dwasf.ordersadjusted AS
    actual_sellers,
      ROUND((dwasf.ordersadjusted / soh.sheets_sent) *(100),   2) AS
    part_percent,
      dwasf.unitsadjusted AS
    actual_units,
      dwasf.dollarsadjusted AS
    actual_dollars,
      ROUND((dwasf.unitsadjusted / dwasf.ordersadjusted),   2) AS
    units_seller,
      soh.delivery_date,
      ROUND((dwasf.dollarsadjusted / dwasf.unitsadjusted),   2) AS
    retail_unit,
      a.sheets_sent AS
    sheetsii,
      a.ordersadjusted AS
    actual_sellersii,
    case when a.sheets_sent = 0
      then 0 
      else (ROUND((a.ordersadjusted / a.sheets_sent) *(100),   2))end AS
    part_percentii,
      a.unitsadjusted AS
    actual_unitsii,
      a.dollarsadjusted AS
    actual_dollarsii,
      ROUND((a.unitsadjusted / a.ordersadjusted),   2) AS
    units_sellerii,
      ROUND((a.dollarsadjusted / a.unitsadjusted),   2) AS
    retail_unitii,
      (dwasf.dollarsadjusted -a.dollarsadjusted) AS
    yr_diff,
      dis.name AS
    dist_name
    FROM dw_account_sales_fact dwasf,
      se_order_header soh,
      company_calendar_tbl cct,
      district_tbl dis,
      account_tbl act Left JOIN
      (SELECT soh.delivery_date as d_date, soh.account_number,
         soh.sheets_sent,
         dwasf.ordersadjusted,
         unitsadjusted,
         dollarsadjusted,
         ROUND((unitsadjusted / dwasf.ordersadjusted),    2) AS
      units_sellerii,
         ROUND((dollarsadjusted / unitsadjusted),    2) AS
      retail_unitii
       FROM dw_account_sales_fact dwasf,
         company_calendar_tbl cct,
         se_order_header soh,
         account_tbl act,
         district_tbl dis
       WHERE soh.order_number = dwasf.saleid
     AND dwasf.business_key = '2'
     AND soh.season = 'FALL 2005'
    AND soh.order_status = 'INVOICED'
     AND soh.account_number = act.account_number
     AND soh.delivery_date = cct.calendar_date
     AND soh.district = dis.id) 
    a ON a.account_number = act.account_number
    WHERE soh.order_number = dwasf.saleid
     AND dwasf.business_key = '2'
     AND soh.season = 'FALL 2006'
    AND soh.order_status = 'INVOICED'
     AND soh.account_number = act.account_number
     AND soh.delivery_date = cct.calendar_date
     AND soh.district = dis.id
     GROUP BY soh.delivery_date, dwasf.saleid, act.account_number, act.account_name, soh.sheets_sent, a.sheets_sent, dwasf.dollarsadjusted, dwasf.ordersadjusted, dwasf.unitsadjusted, a.ordersadjusted, a.unitsadjusted, a.dollarsadjusted, dis.name 
    ORDER BY act.account_number

  4. #4
    Join Date
    May 2007
    Posts
    10
    quick note...the MINUS I just realized what you meant. This wouldn't work as I need any matching values from the inner and outer with the outer being the main driver, so if I have 500 rows returned from inner and 600 from outer I would get 600 rows returned and say only maybe 250 from the inner that match and the rest resulting in NULLS, but always returning the outer results and any matching inner results.

Posting Permissions

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


Click Here to Expand Forum to Full Width