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)? Conversely: mysql> SELECT distinct name > FROM people WHERE EXISTS > (select * from people_city_map where cid = 1); Empty set (0.00 sec) *blink* Shouldn't that produce susan and glenn, who are not in the map table at all? Very confused. "Normal" joins across the tables work as expected. Am I overlooking something, or is there sign of a bug? I'll admit, I'm perplexed over the bit in the docs which says "Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference." Thanks in advance Michael -- Michael Fischer Happiness is a config option. [EMAIL PROTECTED] Recompile and be happy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
