Re: Outer join

From: <>
Date: Thu, 18 Sep 2008 09:26:55 -0700 (PDT)
Message-ID: <>

On Sep 17, 7:49 pm, (Dan Blum) wrote:
> 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                                               
> "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

Original text of this message