By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Selecting distinct values from joined table

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. use AppDb
  2. SELECT Customers.CustomerFirstName, Customers.CustomerLastName, Products.ProductName, Transactions.Quantity, Transactions.OrderTotal, Transactions.PaymentMethod, Transactions.OrderDate
  3. FROM Customers
  4. JOIN Transactions
  5.     ON Customers.CustomerNo = Transactions.CustomerNo
  6. JOIN Products
  7.     ON Products.ProductID = Transactions.ProductID
  8.  
This doesn't work either when I use the DISTINCT keyword:

Expand|Select|Wrap|Line Numbers
  1. use AppDb
  2. SELECT DISTINCT Customers.CustomerFirstName, Customers.CustomerLastName, Products.ProductName, Transactions.Quantity, Transactions.OrderTotal, Transactions.PaymentMethod, Transactions.OrderDate
  3. FROM Customers
  4. JOIN Transactions
  5.     ON Customers.CustomerNo = Transactions.CustomerNo
  6. JOIN Products
  7.     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?
Mar 18 '08 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
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?
Mar 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.