[thelist] what kind of join is this?

Emma Jane Hogbin emmajane at xtrinsic.com
Wed Jul 23 18:51:06 CDT 2003


On Wed, Jul 23, 2003 at 07:22:36PM -0400, rudy wrote:
> the login table has one row per person, right?

Yes.

> the group_membership has one row for every role that a person plays in a
> group
> so PK=(logid, groupid, role)

Yes.

> so one of the problems you had was to select users who "do /not/ appear in
> the group_membership table for a specific group"

Right.
 
> okay, that's easy
> select logid, first, last, middle
>   from login
> left outer
>   join group_membership
>     on login.logid = group_membership.logid
>    and group_membership.groupid = 10
>  where group_membership.logid is null

Perfect! Thanks. When I was playing around I wasn't able to get a NULL
field that I could elimidate [sic] things by. I don't think I've ever done
a join that had two requirements before and I can't imagine I would have
ever figured it out on my own. :)

> basically, you're asking the database to do a left outer join to a specific
> groupid, and for those left table logids where there's no match, they will
> be returned with a null in the logid of the right table

Makes sense now that I see it.
 
> that's your basic WHERE NOT EXISTS (subquery) construction which mysql does
> not support until 4.1

Which I'm not running. :)


*clicks around the web site to look at all the lovely group administration
pages that work now*

emma :)

-- 
Emma Jane Hogbin
[[ 416 417 2868 ][ www.xtrinsic.com ]]


More information about the thelist mailing list