Selecting distinct values from joined table

[sampalmer21]

Hi,

I want to join two tables together and only select the rows that don't have the same customer first name and last name (see my code below). When I run my code with the DISTINCT keyword, it still returns me all of the rows probably because it requires each column to be the same in order to find distinct rows, but I want the rows just as long as the customer first and last names are different from other customers:

use AppDb
SELECT Customers.CustomerFirstName, Customers.CustomerLastName, Products.ProductName, Transactions.Quantity, Transactions.OrderTotal, Transactions.PaymentMethod, Transactions.OrderDate
FROM Customers
JOIN Transactions
    ON Customers.CustomerNo = Transactions.CustomerNo
JOIN Products
    ON Products.ProductID = Transactions.ProductID
 

This doesn't work either when I use the DISTINCT keyword:

use AppDb
SELECT DISTINCT Customers.CustomerFirstName, Customers.CustomerLastName, Products.ProductName, Transactions.Quantity, Transactions.OrderTotal, Transactions.PaymentMethod, Transactions.OrderDate
FROM Customers
JOIN Transactions
    ON Customers.CustomerNo = Transactions.CustomerNo
JOIN Products
    ON Products.ProductID = Transactions.ProductID

I don't care if the Products.ProductName or Transactions.Quantity etc. are equal just as long as the Customers.CustomerFirstName and Customers.CustomerLastName are not equal to other customers, so that I won't get multiple entries for the same customer, how do I go about doing this?

[Delerna]

Hi sampalmer21
The customers table has 1 entry per customer right?
The Transaction table can have many entries for a particular customer, right?

Therefore the join will always re-produce the customer in the result. Once for each entry for that customer in the transaction table.

If you need the customer to be in the result recordset once only then you will need to summarise the transaction table in some way so that there is only one record per customer there also.
So the question is, how would you like to summarise the transactions?