Good one. I don't know how I missed this either! Thanks!
"gerald_clark" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > What about > select distinct a.region, a.city > from mytable a , mytable b > where a.region=b.region and a.city <> b.city > > Jay Blanchard wrote: > > >[snip] > >Anybody? > > > > > >>>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 > >>> > >>> > > > >There is no good way to get this in a single query (w/o subqueries). > >Having applied all sorts of query mangling you would have to be able to > >carry forward some sort of count or variable in order to draw out the > >ones where the foreign key was > 1. Grouping by the city does not work > >either as that reduces any count to a one for that record. > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
