Brent Baisley wrote:
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
No, it won't. It will produce output like this:
+----------+----------+----------+ | LastName | OrderNum | OrderNum | +----------+----------+----------+ | jones | 10 | 5 | | jones | 12 | 5 | | jones | 23 | 5 | | jones | 5 | 10 | | jones | 12 | 10 | | jones | 23 | 10 | | jones | 5 | 12 | | jones | 10 | 12 | | jones | 23 | 12 | | jones | 5 | 23 | | jones | 10 | 23 | | jones | 12 | 23 | | smith | 2 | 1 | | smith | 3 | 1 | | smith | 1 | 2 | | smith | 3 | 2 | | smith | 1 | 3 | | smith | 2 | 3 | +----------+----------+----------+ 18 rows in set (0.08 sec)
Each row in A is paired with each non-matching row in B.
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.
It is. <http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html>
That shows that smith has order 1,2, and 3. Jones has 5,10,12 and 23.
The answer is in there, but it's a mess.
A simple "SELECT LastName, OrderNum FROM orders" would do, except you want to leave out the rows with unique LastName values. The following should work:
# Collect the non-unique last names CREATE TEMPORARY TABLE lastnames SELECT LastName FROM orders GROUP BY LastName HAVING COUNT(*) > 1;
# Find the rows with the non-unique last names SELECT orders.LastName, OrderNum FROM orders, lastnames WHERE orders.Lastname = lastnames.lastname; ORDER BY orders.Lastname, OrderNum;
+----------+----------+ | LastName | OrderNum | +----------+----------+ | jones | 5 | | jones | 10 | | jones | 12 | | jones | 23 | | smith | 1 | | smith | 2 | | smith | 3 | +----------+----------+ 7 rows in set (0.01 sec)
# Clean up DROP TABLE lastnames;
With 4.1 and subqueries, this becomes:
SELECT LastName, OrderNum
FROM orders
WHERE LastName IN (SELECT LastName
FROM orders
GROUP BY LastName
HAVING COUNT(*) > 1)
ORDER BY Lastname, OrderNum;Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
