[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