Re: Rewrite Full Outer Join into Subquery

[Richard House]

Thanks for your response Rick.

"Are you mistakenly tying the date_purchased to the..."
If I understand you correctly, I reckon that I need two instances of dim_date. In this case, dim_date should return datasets for both the Actuals and Projections. Even if either has no dataset for the selected dim_date, it should atleast return 0 or # for that row.

"You gave part of a RIGHT JOIN..UNION..LEFT JOIN, but..."
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_orders_status` S_orders_status ON S_orders.`orders_status` = S_orders_status.`orders_status_id`
INNER JOIN `S_customers` S_customers ON S_orders.`customers_id` = S_customers.`customers_id`
INNER JOIN `S_customers_groups` S_customers_groups ON S_customers.`customers_group_id` = S_customers_groups.`customers_group_id`
LEFT 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:"))
UNION
bla bla bla
RIGHT 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:"))

"RIGHT..UNION DISTINCT..LEFT is the way to get all the rows in one table or another..."
RIGHT..UNION DISTINCT..LEFT sounds good to me if it gets the job done. Just don't know how it works. Will appreciate it greatly though if you show me.

SHOW CREATE TABLE

 
CREATE TABLE `S_orders` (
 `orders_id` int(11) default NULL,
 `customers_id` int(11) default NULL,
 `customers_name` varchar(64) default NULL,
 `date_purchased` date default NULL,
 `orders_status` int(11) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `S_orders_total` (
 `orders_id` int(11) default NULL,
 `title` varchar(255) default NULL,
 `value` double default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `S_customers` (
 `customers_group_id` int(11) default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `S_customers_groups` (
 `customers_group_name` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `S_orders_status_A` (
 `orders_status_id` int(11) default NULL,
 `orders_status_name` varchar(32) default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `dim_date` (
 `date` date NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `S_projection1` (
 `p_budget_date` date default NULL,
 `p_sales_trends_adjust` int(11) default NULL,
 `p_s_bar_prod` double default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Cheers.

Regards,