Re: Outer join

On Sep 17, 7:49 pm, t..._at_panix.com (Dan Blum) wrote:
> a..._at_unsu.com wrote:
> > Hi,
> > This is simple, at least for others.  What's wrong with this?  It only
> > returns the rows in the fund_daily_prices table.....not all the ones
> > in fund_rank  which have no matching in fund_daily_prices .
> > SELECT fund_rank.idnum
> > FROM fund_rank LEFT OUTER JOIN fund_daily_prices ON fund_rank.idnum =
> > fund_daily_prices.idnum
> > WHERE seq_no > 0
> >   AND seq_no <= 50 ORDER BY seq_no;
>
> What table is the seq_no column in? If it's in fund_daily_prices, you need
> to account for it being NULL.
>
> --
> _______________________________________________________________________
> Dan Blum                                                 t..._at_panix.com        
> "I wouldn't have believed it myself if I hadn't just made it up."

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.... Received on Thu Sep 18 2008 - 11:26:55 CDT