#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    355
    Rep Power
    11

    Can it be done in a single query?


    A user can belong to more than one group in >groups, and should be able to see all the texts from >messages. Therefore, and to normalize the database, I created a third >accessMsg, which assigns a message to one or more groups.

    Code:
    Table groups
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(10) NOT NULL
    
    Table messages
    `id` int(11) NOT NULL auto_increment,
    `text` varchar(250) NOT NULL
    
    Table accessMsg
    `id` int(11) NOT NULL auto_increment,
    `msg_id` int(11) NOT NULL,
    `group_id` int(11) NOT NULL


    Example: if
    `messages`.`id`=1 is readable for
    `groups`.`id`=5 and `groups`.`id`=10,

    >accessMsg will have the 2 rows:

    Code:
    `accessMsg`.`msg_id`=1 AND `accessMsg`.`group_id`=5
    
    `accessMsg`.`msg_id`=1 AND `accessMsg`.`group_id`=10
    How could I query such a structure if a user belongs to groups, lets say 2,4,5,10 to let him see all messages that correspond to his groups?
    Can it be done in a single query od should I query >messages and >accessMessages separately and match them with PHP?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by benwenger
    Can it be done in a single query
    yes

    you haven't shown us the user-group relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2005
    Posts
    355
    Rep Power
    11
    Originally Posted by r937
    yes

    you haven't shown us the user-group relationship

    Table userGroups
    `id` int(11) NOT NULL auto_increment,
    `user_id` int(11) NOT NULL,
    `group_id` int(11) NOT NULL,

    So the person from above would have 4 rows in that table

    `user_id`=1 AND `group_id`=2
    `user_id`=1 AND `group_id`=4
    `user_id`=1 AND `group_id`=5
    `user_id`=1 AND `group_id`=10

    anyway the group ids of a person are gathered and stored in a Session upon login :
    $_SESSION['mygroups']=array(2,4,5,10);
    But maybe that is of no use for this particular query.

IMN logo majestic logo threadwatch logo seochat tools logo