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]

Reply via email to