MySQL Forums :: Newbie :: Rewrite Full Outer Join into Subquery


Advanced Search

Rewrite Full Outer Join into Subquery
Posted by: Richard House ()
Date: October 11, 2014 08:14AM

Hi Guys,

I need your help to rewrite a query with inner join and full outer join into a subquery format. Purpose of query is to merge and return datasets from Actuals and Projections tables.
So there are 7 tables (all from 1 database) involved.
5 tables for Actuals (S_orders, S_orders_total, S_orders_status_A, S_customers and S_customers_groups)
1 table for Projections (S_projection1)
1 table for date dimension (dim_dates)

If I use UNION with the query (since you cannot use full outer join in mysql) before the WHERE statement, e.g
RIGHT OUTER JOIN `S_projection1` S_projection1 ON dim_dates.`date` = S_projection1.`p_budget_date`
UNION (and then for the second query use)
LEFT OUTER JOIN `S_projection1` S_projection1 ON dim_dates.`date` = S_projection1.`p_budget_date`
(or vise versa) the query will return only dataset for Actuals and nothing for Projections or vise versa.

I need the query to return datasets for both the Actuals (S_orders_total.`value`) and Projections (S_projection1.`p_s_bar_prod`).
I know it can be done with subquery but my knowledge of subquery is not good enough to achieve this, thus, I need your help.

Thanks a lot in advance.

Regards,
richy-rich

SELECT
     dim_dates.`year2` AS year,
     dim_dates.`date` AS DimDate,
     dim_dates.`day_name` AS DimDay,
     dim_dates.`holidays` AS DimHolidays,
     dim_dates.`week_in_year` AS DimWeek,
     dim_dates.`month_number` AS DimMonthNr,
     dim_dates.`month_name` AS DimMonth,
     dim_dates.`quarter_name` AS DimQuarter,
     dim_dates.`year_quarter` AS DimYearQ,
     dim_dates.`date_key` AS DimDateKey,
     S_orders_total.`orders_id` AS OrdersId,
     S_orders_total.`title` AS OrdersTitle,
     S_orders_total.`value` AS OrdersValue,
     S_orders.`date_purchased` AS OrdersDate,
     S_projection1.`p_budget_date` AS BudgetDate,
     S_orders.`customers_id` AS CustId,
     S_orders.`customers_name` AS CustName,
     S_orders.`customers_company` AS CustCompany,
     S_orders.`customers_street_address` AS CustStreet,
     S_orders.`customers_city` AS CustCity,
     S_orders.`customers_postcode` AS CustPostcode,
     S_orders.`customers_state` AS CustState,
     S_orders.`customers_country` AS CustCountry,
     S_orders.`customers_telephone` AS CustTelephone,
     S_orders.`customers_email_address` AS CustEmail,
     S_customers_groups.`customers_group_name` AS CustomersGroup,
     S_projection1.`p_sales_trends_adjust` AS PsalesTrendsAdjust,
     S_projection1.`p_s_bar_prod` AS PShopBar,
     S_orders_status_A.`orders_status_name` AS OrdersStatus
FROM
     `S_orders` S_orders INNER JOIN `S_orders_total` S_orders_total ON S_orders.`orders_id` = S_orders_total.`orders_id`
     INNER JOIN `dim_dates` dim_dates ON S_orders.`date_purchased` = dim_dates.`date`
     INNER JOIN `S_customers` S_customers ON S_orders.`customers_id` = S_customers.`customers_id`
     INNER JOIN `S_orders_status` S_orders_status_A ON S_orders.`orders_status` = S_orders_status_A.`orders_status_id`
     INNER JOIN `S_customers_groups` S_customers_groups ON S_customers.`customers_group_id` = S_customers_groups.`customers_group_id`
     FULL OUTER JOIN `S_projection1` S_projection1 ON dim_dates.`date` = S_projection1.`p_budget_date`
WHERE
     S_orders.`orders_status` = 3
 AND S_orders_total.`title` IN (("Sub-Total:"),("Subtotal :"),("Sub-Totaal:"))
 

Options: ReplyQuote


Subject Written By Posted
Rewrite Full Outer Join into Subquery Richard House 10/11/2014 08:14AM
Re: Rewrite Full Outer Join into Subquery Peter Brawley 10/11/2014 12:54PM
Re: Rewrite Full Outer Join into Subquery Rick James 10/11/2014 01:09PM
Re: Rewrite Full Outer Join into Subquery Richard House 10/12/2014 09:23AM
Re: Rewrite Full Outer Join into Subquery Richard House 10/12/2014 09:03AM
Re: Rewrite Full Outer Join into Subquery Peter Brawley 10/12/2014 11:13AM
Re: Rewrite Full Outer Join into Subquery Rick James 10/12/2014 04:00PM
Re: Rewrite Full Outer Join into Subquery Richard House 10/14/2014 11:29AM
Re: Rewrite Full Outer Join into Subquery Rick James 10/14/2014 05:51PM
Re: Rewrite Full Outer Join into Subquery Richard House 10/14/2014 11:13AM
Re: Rewrite Full Outer Join into Subquery Peter Brawley 10/14/2014 11:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.