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.Produc tName or Transactions.Qu antity etc. are equal just as long as the Customers.Custo merFirstName and Customers.Custo merLastName 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?