SELECT DISTINCT u.userid, IF( ul.listid, "true", "false")
FROM user u LEFT OUTER JOIN userlist ul ON u.userid = ul.userid
AND ul.listid = <listid>;
-----Original Message-----
From: David Otton [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 10, 2001 5:39 PM
To: [EMAIL PROTECTED]
Subject: Select where A is not a member of B
Hi - I've got an annoying problem here, I've checked books, web,
archives, etc, but can't find anything suitable so far.
I have 2 data tables (ignoring the other columns, not important) :
+--------+
| user |
+--------+
| userid |
+--------+
+--------+
| list |
+--------+
| listid |
+--------+
and a joining table :
+----------+
| userlist |
+----------+
| listid |
| userid |
+----------+
As you can see, users can belong to many lists, lists can contain many
users.
I need to find, for each user, whether they are a member of list n :
+--------+--------------+
| userid | memberoflist |
+--------+--------------+
| 1 | true |
| 2 | false |
| 3 | true |
| 4 | false |
+--------+--------------+
It seems simple, but I've been banging my head against this all
weekend. When I break it down, I need to find 3 things :
Users that are members :
SELECT user.userid FROM user, userlist WHERE listid=1 AND
user.userid=userlist.userid
Users that belong to NO lists :
SELECT user.userid FROM user LEFT JOIN userlist ON
user.userid=userlist.userid WHERE listid IS NULL
Users that belong to some lists, but not the one I'm interested in :
this is the one that I'm stuck on. Any thoughts? Suggestions?
djo
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
