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
