Anybody? ""Jeff Burgoon"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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]
