-
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....
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|