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]
