Re: Outer join

On 18 Sep, 17:26, mje..._at_gmail.com wrote:
>
> Hi Dan,
>
> Here is another one that does not seem to be working.  I'm not sure
> what I am doing wrong........
>
> SELECT DISTINCT(e.customer_id),
> DECODE(action,'A','Active','D','Inactive') "Status", action_date,
>        MAX(action_date) KEEP (DENSE_RANK LAST ORDER BY action_date)
> OVER (PARTITION BY e.customer_id) "STUFF"
> FROM email_product_hist e LEFT OUTER JOIN  subscr_email_product s ON
> e.customer_id = s.customer_id
> WHERE action_date BETWEEN TO_DATE('06012008','MMDDYYYY') AND
> TO_DATE('09152008','MMDDYYYY')
>   AND e.email_product_id = 'PPM'
> ORDER BY e.customer_id DESC, action_date DESC;
>
> According to the documentation, the LEFT JOIN should return all rows
> from email_product_hist and rows from subscr_email_product  that do
> not have matching rows....
>

Is action_date in the subscr_email_product table? You didn't include an alias so I can't be sure.

WHERE is evaluated AFTER the outer join so that WHERE condition would exclude the NULLs if it refers to the unpreserved ("right") table.

If I'm right then try changing it like this:

...
FROM email_product_hist e
LEFT OUTER JOIN subscr_email_product s
ON e.customer_id = s.customer_id
AND s.action_date BETWEEN TO_DATE('06012008','MMDDYYYY') WHERE TO_DATE('09152008','MMDDYYYY')
  AND e.email_product_id = 'PPM'
...

--
David Portas