SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    many to many query, comma delimited

    Hey guys

    This should be a simple yes or no answer, I think .

    I have 3 database tables :
    users
    groups
    user_group

    The third table has a many to many relationship from user<->group. So one user can belong to multiple groups and multiple users can belong to one group.

    Originally I had one row per user/group relationship. ie.

    Code:
    user_id   |  group_id
              1	       1
              1	       2
              1	       3
    So I thought I'd be really clever and reduce the size of the table by having only one entry per user, with a comma delimited entry for the groups they belong to.
    So :
    Code:
    user_id   |  group_id
           1		1, 2, 3
           2		1,3
    Now I want to perform a query using the comma delimited list, but it doesn't seem to work.
    Here is my query :

    Code:
    SELECT usergroup.user_id, usergroup.group_id, groups.group_id, 
         groups.group_name
         FROM usergroup, groups
                    WHERE usergroup.user_id = '$_SESSION[uid]'
          AND groups.group_id IN (usergroup.group_id)
    This query works if I replace this: (usergroup.group_id) with this : (1,2,3).

    So my question is, is it possible to do a WHERE IN query using a column like that? Or would I need to do two seperate queries?

    Thanks for the help
    Marc

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't use a list within a column/field it violates first normal form and does nothing to speed up your database queries.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DannyTCOTW
    So my question is, is it possible to do a WHERE IN query using a column like that? Or would I need to do two seperate queries?
    yes, it is, but it will be painfully inefficient, and always require a table scan

    you would not be saving a meaningful amount of space, and you'd be paying for it with a really slow application

    i strongly urge you to go back to the way you had it, one row per user/group

    that is the correct design, and queries based on it will be able to use indexes for maximum efficiency
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah thanks.

    Well it's good to know that I did have the right idea at some point, before deviating into unknown waters.

    Thanks for the help, you guys are great!

    M

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have another question, if you don't mind.

    This too may be a novice design error on my side, so I'm hoping there's a short answer to this one too.

    In my previous example I had users and groups. In my actual db, I have users, groups and sections.

    The user<->group relationship is many to many (with a joining table) and the group<->section is many to many (again with a joining table).
    What I need to do is figure out which sections a user is allowed access to. My code, which works, is a little clunky and I really think there should be a better way of doing this... even though I can't seem to figure it out.

    So the flow looks something like this :

    users -> usergroup -> groups -> sectiongroup -> sections.

    Although, I've gotten rid of the middle 'group', since the groupid is replicated in the two joining tables.
    At the moment I have 3 queries to return which section the user can access, but it seems like there should be less, or perhaps a cleaner design.

    Anyone managed something like this before in a simple and elegant manner?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    users -> usergroup -> sectiongroup -> sections

    one query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Location
    LA, California
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok good, at least I'm on the right track.

    BTW, I just discovered your "Ask the expert" answer on joining two many-to-many tables (through google oddly enough), and I'm reading through it now.

    Thanks again, I appreciate the help.
    Marc


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
  •