>Both table contain acctnum1 and acctnum2. Acctnum2 is a trailer, a different address for the same customer. For example Barnes and Noble has many stores, their accounts would look like the following
>Acctnum1 Acctnum2
>12345 0001
>12345 0002
>12345 0003
>All would be Barnes and Noble but different addresses. A combination of the two is a key.
>
>I'd like to do a join on a result set, and I don't know if thats possible.
>It would be something like:
>
>select * from customers where acctnum1, acctnum2 in (select distinct acctnum1, acctnum2 from invoices where invoicedate>05/01/1998)
>
>I know this doesn't work so I was thinking I could do a join of the subquery with the customer file. I'm not sure how though.
Hi Gregg,
Why do you want to SELECT DISTINCT? If you want to find the customers that did business with your company after 5/01/1998 you can just

Select Distinct a.* ;
  From Customers a, Invoices b ;
  Where a.Acctnum1=b.Acctnum1 And a.Acctnum2=b.Acctnum2 ;
        And b.Invoicedate>05/01/1998

An index on acctnum1+acctnum2 in both tables will speed up the query. Here the DISTINCT will give you one row for every different customer. HTH