SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    australia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Is this just an imposible task ?

    Ok, I have been banging my head against my monitor for a day and a half now!
    I have 3 tables:

    mysql> select * from groups;
    +----------+-----------------------------+---------------+--------+
    | group_id | group_desc | group_type_id | active |
    +----------+-----------------------------+---------------+--------+
    | 1 | Main Group | 1 | t |
    | 2 | Franchisee | 2 | t |
    | 3 | Clinic | 3 | t |
    +----------+-----------------------------+---------------+--------+


    mysql> select * from modules;
    +-----------+---------------------+--------+
    | module_id | module_desc | active |
    +-----------+---------------------+--------+
    | 1 | admin_modules | t |
    | 2 | admin_groups | t |
    | 3 | admin_group_details | t |
    | 4 | admin_group_test | t |
    +-----------+---------------------+--------+


    mysql> select * from group_modules;
    +----------+-----------+
    | group_id | module_id |
    +----------+-----------+
    | 1 | 1 |
    | 1 | 2 |
    | 1 | 3 |
    | 2 | 2 |
    | 2 | 3 |
    | 3 | 3 |
    +----------+-----------+


    What I want as a result are the modules that are currently not in the group_modules table.

    ie: For group_id = 1, I want the result of module_id 4 only,
    For group_id = 2, I want module_id 1 & 4,
    And group_id = 3, I want module_id 1,2 & 4.

    I am not having troubles in getting the modules the groups do have access to, I need the ones they currently dont have access to.

    Please help out with a Query.

    Thanks
    CrackBabie

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select m.module_id, gm.group_id
    from modules m left join group_modules gm
    on m.module_id = gm.module_id
    where gm.module_id is null

  3. #3
    SitePoint Member
    Join Date
    Jun 2004
    Location
    australia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql> select m.module_id, gm.group_id
    -> from modules m left join group_modules gm
    -> on m.module_id = gm.module_id
    -> where gm.module_id is null;
    Empty set (0.00 sec)

    I wish that would have worked

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select m.module_id , g.group_id
    from modules m, group g
    left join group_module gm             
    on m.module_id = gm.module_id 
    and g.group_id = gm.group_id
    where gm.mid is null

  5. #5
    SitePoint Member
    Join Date
    Jun 2004
    Location
    australia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THANk Yo!

    mysql> select m.module_id, g.group_id
    -> from modules m, groups g
    -> left join group_modules gm
    -> on m.module_id = gm.module_id
    -> and g.group_id = gm.group_id
    -> where gm.module_id is null
    -> AND g.group_id = '1';
    +-----------+----------+
    | module_id | group_id |
    +-----------+----------+
    | 4 | 1 |
    +-----------+----------+
    1 row in set (0.00 sec)


    perfect, exactly what I wanted ...

    can you please do me a favour and explain why / how you came about this query, as I am still baffled.

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    from modules m, groups g
    This will return all combinations of groups and modules.

    Code:
    left join group_modules gm
    on m.module_id = gm.module_id
    When doing a left join, columns for the nonmatching records in the table to the right (gm) are filled with null.

    You could run this query

    Code:
    select m.module_id, g.group_id, gm.module_id
    from modules m, groups g
    left join group_modules gm
    on m.module_id = gm.module_id
    and g.group_id = gm.group_id
    to see that all nonmatching rows have null in the gm.module_id column. By applying the condition

    Code:
    where gm.module_id is null
    only the nonmatching rows are returned.


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
  •