This also works... SELECT name FROM people AS p JOIN people_city_map AS pcm ON (p.id = pcm.pid) WHERE pcm.cid = 1;
Not sure if it is any faster (or better) than what Chris suggested but it seems to be faster on my machine. Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -----Original Message----- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 1:02 AM To: Michael Fischer Cc: mysql@lists.mysql.com Subject: Re: WHERE (NOT) EXISTS problem Michael Fischer wrote: > Having a very bad time with the subject sorts of queries. > Here is a simple reproduction of the problem for me. > Perhaps I'm blind/stupid while looking at the docs, > or there's a bug... > > mysql version 5.0.24-standard > > simple schema: > > mysql> desc people; > +-------+--------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +-------+--------------+------+-----+---------+----------------+ > | id | int(11) | NO | PRI | NULL | auto_increment | > | name | varchar(255) | YES | | NULL | | > +-------+--------------+------+-----+---------+----------------+ > > mysql> desc cities; > +-------+--------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +-------+--------------+------+-----+---------+----------------+ > | id | int(11) | NO | PRI | NULL | auto_increment | > | name | varchar(255) | YES | | NULL | | > +-------+--------------+------+-----+---------+----------------+ > > mysql> desc people_city_map; > +-------+---------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------+---------+------+-----+---------+-------+ > | pid | int(11) | NO | | | | > | cid | int(11) | NO | | | | > +-------+---------+------+-----+---------+-------+ > > > mysql> select * from people; > +----+---------+ > | id | name | > +----+---------+ > | 1 | michael | > | 2 | daniel | > | 3 | glenn | > | 4 | susan | > | 5 | lisa | > | 6 | reggie | > +----+---------+ > > mysql> select * from cities; > +----+----------+ > | id | name | > +----+----------+ > | 1 | new york | > | 2 | boston | > | 3 | chicago | > +----+----------+ > > mysql> select * from people_city_map; > +-----+-----+ > | pid | cid | > +-----+-----+ > | 1 | 1 | > | 2 | 1 | > | 5 | 3 | > | 6 | 3 | > +-----+-----+ > > > Now, not that this is the best way to do things generally, but, after all, > this is a simplification of a more complex real app in development, so... > > mysql> SELECT distinct name >> FROM people WHERE EXISTS >> (select * from people_city_map where cid = 1); > > +---------+ > | name | > +---------+ > | michael | > | daniel | > | glenn | > | susan | > | lisa | > | reggie | > +---------+ > > Huh? Shouldn't that be limited to the people mapped to New York (michael, > daniel)? No. Simple misunderstanding of the docs. EXISTS returns TRUE if it finds *any* result. http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries. html You want: select distinct name from people where pid in (select pid from people_city_map where cid=1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
