If you are using 4.1, you can look into the GROUP_CONCAT function.
Otherwise, try a self join like this: SELECT A.LastName,A.OrderNum,B.OrderNum FROM Orders AS A LEFT JOIN Orders AS B ON A.LastName=B.LastName WHERE A.OrderNum!=B.OrderNum ORDER BY A.LastName
That joins the Order table with itself on LastName and filters out the non-duplicates, since there will always be at least one match, by filtering out Order Numbers that match for the duplicate last names. It will output something like this:
A.LastName A.Order B.OrderNum ----------------------------------------------------- smith 1 2 smith 1 3 jones 5 10 jones 5 12 jones 5 23
I don't know if the not equal syntax I used "!=" is valid for your version of MySQL. I know it works in 4.1.
That shows that smith has order 1,2, and 3. Jones has 5,10,12 and 23.
On Sep 30, 2004, at 2:22 PM, Paul Fine wrote:
I am trying to come up with a query that shows duplicate last names and the
"order numbers" for each occurance of.
I can get as far as determining the duplicates but my query result only outputs one order for each.
Here is my current query, an example of the results and an example of the
results I want.
P.S. This is just an example, looking at duplicate last names is seldom of
any practical value!
Thanks for any help!
customer_last_name order_number +-------------------+-------------+ +smith + 1 + +smith + 2 + +smith + 3 + +---------------------------------+
SELECT customer_last_name, order_number, COUNT(customer_last_name) AS duplicate_customer_last_names
FROM orders
GROUP BY customer_last_name HAVING (duplicate_customer_last_names > 1)
Result:
customer_last_name order_number duplicate_customer_last_names +-------------------+-------------+---------------------------------+ +smith + 1 + 3 + +---------------------------------+---------------------------------+
Desired Result:
customer_last_name order_number duplicate_customer_last_names +-------------------+-------------+---------------------------------+ +smith + 1 + 3 + +smith + 2 + 3 + +smith + 3 + 3 + +---------------------------------+---------------------------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
