Konstantin,
Thursday, March 14, 2002, 1:04:42 PM, you wrote:
KT> : denonymous,
KT> : Wednesday, March 13, 2002, 9:06:48 PM, you wrote:
KT> :
KT> : d>> Hi all -- I just need a quick sanity check on a query.
KT> :
KT> :
KT> : d>> I have two tables:
KT> :
KT> : d>> CREATE TABLE users (
KT> : d>> userID VARCHAR(10) NOT NULL PRIMARY KEY,
KT> : d>> [many other fields]
KT> : d>> )
KT> :
KT> : d>> CREATE TABLE subscriptions (
KT> : d>> userID VARCHAR(10) NOT NULL,
KT> : d>> listID INT NOT NULL
KT> : d>> )
KT> :
KT> :
KT> : d>> In these tables, users.userID == subscriptions.userID.
KT> : d>> The users table keeps 1 record per userID, and the subscriptions table
KT> keep
KT> : d>> multiple records per userID (one for each listID to which they're
KT> : d>> subscribed).
KT> :
KT> : d>> If I wanted to select all users from the users table that *do not*
KT> appear in
KT> : d>> the subscriptions table, I'd want the following query:
KT> :
KT> : d>> SELECT users.*
KT> : d>> FROM users
KT> : d>> LEFT JOIN subscriptions
KT> : d>> ON users.userID=subscriptions.userID
KT> : d>> WHERE subscriptions.userID IS NULL
KT> subscriptions.userID will never be NULL (the field is created that way).
KT> maybe a good idea is to let it be NULL so you can tell when there is no
KT> coresponding record in the users table.
Take a closer look at the manual:
http://www.mysql.com/doc/J/O/JOIN.html
Look at:
mysql> select * from users;
+--------+
| userid |
+--------+
| egor |
| sasha |
| vita |
+--------+
mysql> select * from subscriptions;
+--------+--------+
| userid | listid |
+--------+--------+
| vita | 1 |
| vita | 2 |
| masha | 3 |
| cate | 4 |
+--------+--------+
4 rows in set (0.00 sec)
mysql> select * from users left join subscriptions on
users.userid=subscriptions.userid;
+--------+--------+--------+
| userid | userid | listid |
+--------+--------+--------+
| egor | NULL | NULL |
| sasha | NULL | NULL |
| vita | vita | 1 |
| vita | vita | 2 |
+--------+--------+--------+
4 rows in set (0.01 sec)
mysql> select users.userid from users left join subscriptions on
users.userid=subscriptions.userid where subscriptions.userid is null;
+--------+
| userid |
+--------+
| egor |
| sasha |
+--------+
2 rows in set (0.00 sec)
That's all.
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
