VBStarterKid
Member
Hello there, I am stuck as to how to sequence an SQL statement to select all customer names from the Customer table and order the list by PurchaseDate. The structure of the tables are like the following:
The relation between the Customer table and Booking table is linked by a primary key CustomerID in Customer and foreign key CustomerID in Booking table. The relationship is a one-to-many, whereby one customer can make multiple purchases. In order to get all customer names, regardless of whether they have made a purchase or not, I'm joining both the tables with:
Currently my statement returns multiple customer names if he/she has records of multiple purchases in the Booking table. Note that the ORDER BY Booking.PurchaseDate is very important because I need to sort the list by giving priority to the customers who made more recent purchases to the top of the result list.
I tried to put a DISTINCT keyword to the Customer.ID and hoped the query would return each customer ID occurence only once but the returned result does not change from the above code:
Is there any way I can achieve what I wanted?? I'd appreciate help because it's quite needed for my project and if possible I'd want to select distinctive customer details at SQL query level and not VB.Net code level because of performance reasons. Thank you.
The relation between the Customer table and Booking table is linked by a primary key CustomerID in Customer and foreign key CustomerID in Booking table. The relationship is a one-to-many, whereby one customer can make multiple purchases. In order to get all customer names, regardless of whether they have made a purchase or not, I'm joining both the tables with:
VB.NET:
SELECT Customer.CustomerID, Customer.Name, Booking.PurchaseDate
FROM Customer
LEFT OUTER JOIN Booking ON Customer.CustomerID = Booking.CustomerID
ORDER BY Booking.PurchaseDate DESC
Currently my statement returns multiple customer names if he/she has records of multiple purchases in the Booking table. Note that the ORDER BY Booking.PurchaseDate is very important because I need to sort the list by giving priority to the customers who made more recent purchases to the top of the result list.
I tried to put a DISTINCT keyword to the Customer.ID and hoped the query would return each customer ID occurence only once but the returned result does not change from the above code:
VB.NET:
SELECT DISTINCT Customer.CustomerID, Customer.Name, Booking.PurchaseDate
FROM Customer
LEFT OUTER JOIN Booking ON Customer.CustomerID = Booking.CustomerID
ORDER BY Booking.PurchaseDate DESC
Is there any way I can achieve what I wanted?? I'd appreciate help because it's quite needed for my project and if possible I'd want to select distinctive customer details at SQL query level and not VB.Net code level because of performance reasons. Thank you.