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:"))