Hello Michael. Here is the query you wanted - you were almost there.
SELECT DISTINCT NAME FROM PEOPLE
WHERE ID IN
(SELECT PID FROM PEOPLE_CITY_MAP
WHERE CID = 1)
Thanks.
-----Original Message-----
From: Michael Fischer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 31, 2007 11:53 PM
To: mysql@lists.mysql.com
Subject: WHERE (NOT) EXISTS problem
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]
********************************************
This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.
If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.
Thank you.
********************************************
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
