How do I get match data from one table twice?

I have a two tables. One is my product description table, the other is
my Orders table. I have two fields in the Order Table that match one
field in the Product table. I want to get the description for the new
product and I want to get the description of the old product. How do I
get this using a query. Below is my current query but this only brings
back a null value for the old product description and the correct
description for the new product.

Select Orders.Product as NewProd, Productn.Desc as New_Desc,;
Orders.Old_Prod, OLD.Desc as Old_Desc, Orders.inumber as Invoice;
from Orders;
inner join Productn on Orders.Productn = Productn.Productn;
left join Productn as OLD on Orders.Old_Prod = Productn.Productn;
where !empty(Orders.Old_Prod)

John
Wed Sep 15 15:44:56 CDT 2004

Hey Trini,

Looks like you need to change your last join to:

left join Productn as OLD on Orders.Old_Prod = OLD.Productn

HTH,

John

"TriniEllis" <psturge@aatbs.com> wrote in message
news:e0432304.0409151009.56a7e9a7@posting.google.com...
> I have a two tables. One is my product description table, the other is
> my Orders table. I have two fields in the Order Table that match one
> field in the Product table. I want to get the description for the new
> product and I want to get the description of the old product. How do I
> get this using a query. Below is my current query but this only brings
> back a null value for the old product description and the correct
> description for the new product.
>
> Select Orders.Product as NewProd, Productn.Desc as New_Desc,;
> Orders.Old_Prod, OLD.Desc as Old_Desc, Orders.inumber as Invoice;
> from Orders;
> inner join Productn on Orders.Productn = Productn.Productn;
> left join Productn as OLD on Orders.Old_Prod = Productn.Productn;
> where !empty(Orders.Old_Prod)