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
RE: Select where A is not a member of B
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
Re: Select where A is not a member of B
[EMAIL PROTECTED] writes: [snipped fscking ML bullshit] database,sql,query,table David Otton writes: 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? SELECT DISTINCT userid FROM userlist WHERE listid != thislistid might be what you want. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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
Re: Select where A is not a member of B
Hi. On Mon, Sep 10, 2001 at 02:38:56PM -0700, [EMAIL PROTECTED] wrote: [...] 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 | ++--+ The following should give something like the above (verified): SELECT u.userid, IF( COUNT(ul.listid) 0, 'true', 'false' ) FROM user u LEFT JOIN userlist ul ON u.userid = ul.userid AND ul.listid = 1 GROUP BY u.userid 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? This would be (not verified): SELECT DISTINCT u.userid FROM user u, userlist ul LEFT JOIN userlist nl ON nl.userid = ul.userid AND nl.listid = 1 WHERE ul.userid = u.userid AND nl.listid IS NULL That is: First build a list of all users that are members in any group, then look up which users are in list 1 and exclude them, then remove all duplicates (DISTINCT). Bye, Benjamin. PS: Feeding the filter: database - 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
