Tricky JOIN

[atiqraza]

I have a tricky question,
I have two tables,
I want to join the two tables on CustomerNO,I only want to retain all the values of Table1. SO i will use an outer join. But During the join i only want to retrieve the last purchaseID, which will be the greater one. If there a way i can do this. in which i can select the max(PurchaseID) during the join so i only have all the values of the first table and the maximum of the CustomerNO

Lets say
Table1

Name CustomerNo
a 1111
b 1112
c 1113

Table 2

CusomerNO PuchaseID
1111 0999
1112 111
1112 112

 

[PHV]

SELECT A.Name, A.CustomerNo, MAX(B.PurchaseID) LastPurchaseID
FROM Table1 A
LEFT JOIN Table2 B ON A.CustomerNo = B.CustomerNO
GROUP BY A.Name, A.CustomerNo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[atiqraza]

Thnx alot buddy, I had been stuck on this for close to a day, i thought about your syntax but was still confused.

Once again thnx a bunch

 

[JarlH]

Another approach:

SELECT A.Name,
       A.CustomerNo,
       (SELECT MAX(B.PurchaseID) FROM Table2 B
        WHERE A.CustomerNo = B.CustomerNo) LastPurchaseID
FROM Table1 A

May execute faster than a GROUP BY query. (Depending on dbms.)