Link to home
Start Free TrialLog in
Avatar of hungoveragain
hungoveragain

asked on

SQL JOIN to multiple records - bring back only the first

I need to join two tables to find the first instance in the second table, however as I am using Teradata I can't use temporary tables, table variables or recursion.

I have two tables - let's call them Customers and Purchases

Customers example
customerid   name
01       Andy Anderson
02       Bob Bobman
03       Chris Cross
04       David Daverton

Purchase example
productid   customerid   datepurchased
02               01                   01/01/2001
02              04                    02/02/2002
01              01                    05/05/2005
07               04                    07/07/2007
06               03                    04/04/2004

I need to left join customers to purchases to bring back details of their first purchase - i.e.
customerid    name     product_first_purchased   date_of_first_purchase
01              Andy Anderson    02      01/01/2001
02              Bob Bobman         NULL     NULL
03              Chris Cross          03        04/04/2004
04              David Daverton    02        02/02/2002

As I said above I can't use any form of temporary table or table variable - it needs to be done in one breath.

How?

Thanks

Mike
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hungoveragain
hungoveragain

ASKER

PERFECT - THANKS.

M