MySQL Forums
Forum List  »  Newbie

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




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.