Simple SQL Query

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 Received on Mon Jul 08 2002 - 19:35:04 CDT