Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Union? Multiple outer joins? What's best here?

    Hi,

    I've had a question up on CodingForums - it seems to be a little more complex than I'd anticipated.

    As briefly as I can: I have tables for Users, Companies, and Projects. A user can belong to several companies; a company can have several projects; a project can have several companies. It's many-to-many all over the place.

    Schema is:

    Code:
    CREATE TABLE  `tblCompanies` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `company_name` varchar(50) NOT NULL,
      PRIMARY KEY  (`id`)
    )
    
    CREATE TABLE  `tblUsers` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `username` varchar(10) NOT NULL,
      `password` varchar(10) NOT NULL,
      `displayname` varchar(50) NOT NULL,
      `email` varchar(150) NOT NULL,
      PRIMARY KEY  (`id`)
    )
    
    CREATE TABLE  `tblProjects` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `project_name` varchar(50) NOT NULL,
      `code` varchar(10) NOT NULL,
      `description` text,
      PRIMARY KEY  (`id`)
    )
    
    CREATE TABLE  `tblUsersCompanies` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `user_id` int(10) unsigned NOT NULL,
      `company_id` int(10) unsigned NOT NULL,
      PRIMARY KEY  (`id`)
    )
    
    CREATE TABLE  `tblCompaniesProjects` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `company_id` int(10) unsigned NOT NULL,
      `project_id` int(10) unsigned NOT NULL,
      `is_default_company` int(10) unsigned default NULL,
      PRIMARY KEY  (`id`)
    )
    
    CREATE TABLE  `tblUsersProjects` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `user_id` int(10) unsigned NOT NULL,
      `project_id` int(10) unsigned NOT NULL,
      PRIMARY KEY  (`id`)
    )

    For a "user details" page, I want:
    All the projects that [all the companies that the user has been assigned to], have been assigned to.
    It should have a column indicating whether or not the user has been assigned to that project.
    The suggested solution so far is:
    SELECT u.username, up.project_id, 'ASSIGNED'
    FROM tblUsers as u
    JOIN tblUsersProjects as up
    ON up.user_id = u.user_id
    WHERE u.user_id = 100001
    UNION
    SELECT u.username, cp.project_id, 'NOT ASSIGNED'
    FROM tblUsers as u
    JOIN tblUsersCompanies as uc
    ON uc.user_id = u.user_id
    JOIN tblCompaniesProjects as cp
    ON uc.company_id = cp.company_id
    WHERE u.user_id = 100001
    But, as the guy points out, this results in duplicate records (I just want a single row in the resultset for each unique project) and might not be the most efficient way of doing it.

    Does anyone have any other suggestions?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT u.username
         , cp.project_id
         , case when up.user_id is null
                then 'NOT ASSIGNED'
                else 'ASSIGNED' end
      FROM tblUsers as u
    INNER
      JOIN tblUsersCompanies as uc
        ON uc.user_id = u.user_id
    INNER
      JOIN tblCompaniesProjects as cp
        ON cp.company_id = uc.company_id
    LEFT OUTER
      JOIN tblUsersProjects as up
        ON up.project_id = cp.project_id
       AND up.user_id = u.user_id
     WHERE u.user_id = 100001
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Many thanks Works perfectly (although I changed u.user_id to u.id - I assume that's what you meant to put.)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, sometimes i copy/paste where i should just type, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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