SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Left join madness

    Having another joyous experience with lookup tables with many to many relationships.

    Have the following tables;

    TABLE groupslookup (
    uid int(11) DEFAULT '0' NOT NULL,
    gid int(11) DEFAULT '0' NOT NULL,
    PRIMARY KEY (uid, gid)
    );

    TABLE doclookup (
    did int(11) DEFAULT '0' NOT NULL,
    gid int(11) DEFAULT '0' NOT NULL,
    PRIMARY KEY (did, gid)
    );

    uid is a users id. gid stands for groups (one user can be in many groups. one group can contain many users).

    did is for document id and each document can be view by many groups and many groups my view a document.

    So basically when someone tries looks at a document, I want to check they are in a group that has permission to view that document.

    I'm guessing a LEFT join comes into it somewhere my attempts so far have failed (I'm getting back more rows that I want).

    How can I get this right with a MySQL query?

    Many thanks
    Last edited by HarryF; Jan 15, 2002 at 12:13.

  2. #2
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think you need to do a left join, since if the join fails it means the person isn't able to view the document. Try the following:

    "SELECT uid
    FROM doclookup a, grouplookup b
    WHERE a.gid = b.gid
    AND did = $doc
    AND uid = $user";

    If it returns zero rows then the user isn't allow to view the document.

  3. #3
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks CF - works perfectly. Think I've been outsmarting myself.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •