title: MySQL Join across multiple tables
body: <p>My web application requires a list of all user groups, the group members, and a list of products that they have access to based on their group membership. </p>
  
  <p>The web application supports multiple "sites"; a site being a unique identifier for those users, groups and products. </p>
  
  <p>I have the following query:</p>
  
  <pre><code>SELECT u.id as user_id, u.username, ug.group_id, ug.group_name, u.firstname, u.surname, p.product_name, p.product_id, p.product_price, p.product_currency, p.is_public
      FROM user_groups ug
      LEFT JOIN user_group_users ugu ON ug.group_id = ugu.group_id
      RIGHT JOIN users u ON u.id  = ugu.user_id
      LEFT JOIN user_group_product ugp on ug.group_id = ugp.group_id
      LEFT JOIN product p on p.product_id = ugp.product_id
      WHERE u.site_id = 5
      ORDER BY ug.group_name desc, u.surname, u.firstname, u.username
  </code></pre>
  
  <p>This is working well, and I concatenate the results using PHP to deliver the data.</p>
  
  <p><strong>QUESTION</strong>: The <code>PRODUCTS</code> table also has an <code>is_public</code> flag, and I would like to include all products in the result set that ALSO have this set to 1 as well as return products that the user has group access to, but again, only for this site.</p>
  
  <p>So, for clarity, I wish to return:</p>
  
  <ul>
  <li>All user records - no matter if they have group membership (ungrouped users will be displayed)</li>
  <li>All user records with associated group membership</li>
  <li>All groups</li>
  <li>All products - where a user is a member of the group</li>
  <li>All products - where the product is set to public - is_public = 1</li>
  </ul>
  
  <p>All of the above must only be for the site in question.</p>
  
  <p><strong>UPDATE 3</strong>: Updated description of requirments</p>
  
  <p><strong>UPDATE 2</strong>: Tables structures:</p>
  
  <pre><code>--
  -- Table structure for table `user_group_users`
  --
  
  CREATE TABLE IF NOT EXISTS `user_group_users` (
    `user_group_id` bigint(20) unsigned NOT NULL auto_increment,
    `user_id` mediumint(9) NOT NULL default '0',
    `group_id` mediumint(9) NOT NULL default '0',
    `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (`user_group_id`),
    KEY `user_id` (`user_id`,`group_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=54 ;
  
  --
  -- Table structure for table `user_groups`
  --
  
  CREATE TABLE IF NOT EXISTS `user_groups` (
    `group_id` mediumint(8) unsigned NOT NULL auto_increment,
    `site_id` mediumint(9) NOT NULL,
    `group_name` varchar(100) NOT NULL default '',
    `is_default` tinyint(1) NOT NULL COMMENT 'Users are automatically added to default groups at registration',
    PRIMARY KEY  (`group_id`),
    KEY `site_id` (`site_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
  
  --
  -- Table structure for table `user_group_product`
  --
  
  CREATE TABLE IF NOT EXISTS `user_group_product` (
    `user_group_product_id` bigint(20) unsigned NOT NULL auto_increment,
    `group_id` mediumint(9) NOT NULL default '0',
    `product_id` mediumint(9) NOT NULL default '0',
    `created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (`user_group_product_id`),
    KEY `product_id` (`product_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=124 ;
  
  --
  -- Table structure for table `product`
  --
  
  CREATE TABLE IF NOT EXISTS `product` (
    `product_id` mediumint(8) unsigned NOT NULL auto_increment,
    `payment_enabled` tinyint(1) NOT NULL default '0',
    `credit_enabled` tinyint(1) NOT NULL default '0',
    `product_name` varchar(100) NOT NULL default 'Default Product',
    `is_public` tinyint(1) NOT NULL default '1',
    `product_price` decimal(10,2) default NULL,
    `product_currency` varchar(5) default NULL,
    PRIMARY KEY  (`product_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;
  </code></pre>