• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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.



1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the db you are using?

you can have this:
select c.*, p.*
  from customers c
  left join purchases p
    on p.customerid = c.customerid
  and p.datepurchased = ( select min(f.datepurchased) from purchases f where f.customerid = c.customerid )

Open in new window

or with oracle/sql2005+:
select sq.*
  from ( select c.*, p.*, row_number() over (partition by c.customerid order by p.purchasedate ) rn
  from customers c
  left join purchases p
    on p.customerid = c.customerid
) sq
where sq.rn = 1 

Open in new window

hungoveragainAuthor Commented:


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now