Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported)
""Jeff Burgoon"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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]
