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]
