Re: Simple SQL Query

Sybrand Bakker wrote:
>
> On 8 Jul 2002 17:35:04 -0700, vpg0002_at_hotmail.com (Vishwa Gaddamanugu)
> wrote:
>
> >I am performing the following query and running into problems:
> >
> >Table:
> >ID New Name PrevID
> >===== ============== ======
> >1 Old A
> >2 Old B
> >3 Old C
> >4 Old D
> >5 Old E
> >6 New A 1
> >7 New B 2
> >8 New C 3
> >9 New D 4
> >10 New E 5
> >
> >Query:
> >SELECT movies.ID, movies.new_name "New Name", mov.new_name "Prev Name"
> > FROM movies, movies mov
> > WHERE movies.ID = mov.ID
> > AND movies.PrevID Is Not Null;
> >
> >Result:
> >ID New Name Prev Name
> >===== ============== =========
> >6 New A New A
> >7 New B New B
> >8 New C New C
> >9 New D New D
> >10 New E New E
> >
> >Result Wanted:
> >
> >ID New Name Prev Name
> >===== ============== =========
> >6 New A Old A
> >7 New B Old B
> >8 New C Old C
> >9 New D Old D
> >10 New E Old E
> >
> >Can some kind soul point out how to tackle this problem?
> >
> >Thanks
> >VeeGee
>
> where movies.id = mov.previousid(+)
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Sybrand,
You are slipping, you put the "previous" on the wrong side:

  where movies.previousid = mov.id(+)

And for this particular example someone might be tempted to add:

  and mov.previousid IS NULL

But that would only hold true if the name cam be changed only once. (Successions like this are an interesting problem.)

 HTH
  Ed

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com