I have a simple problem and I'm just wondering the BEST query to solve it.
I want to return all the rows of a table whose foreign key value exists more
than once in that table. IE...
MyTable
Region(foreign key) City
East Baltimore
East Philly
East Newark
Midwest Cleveland
South Fort Lauderdale
West Phoenix
West Los Angeles
I want a query that returns only the rows where there are more than one of
that particular Region in MyTable. The values returned would be
East Baltimore
East Philly
East Newark
West Phoenix
West Los Angeles
Here is what I'd like to do (but can't because the current stable build of
MySQL doesn't support subqueries)
SELECT MyTable.*
FROM (SELECT Region, Count(*) as cnt
FROM MyTable
GROUP BY Region
HAVING cnt >= 2) as Duplicates,
MyTable
WHERE Duplicates.Region = MyTable.Region
Here is what I'm actually doing:
CREATE TEMPORARY TABLE Duplicates
SELECT Region, Count(*) as cnt
FROM MyTable
GROUP BY Region
HAVING cnt >= 2;
SELECT MyTable.*
FROM MyTable, Duplicates
WHERE MyTable.Region = Duplicates.Region;
Can anybody tell me if there is a more efficient way of doing this query?
Thanks!
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
