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

Spudhead · #1 (**permalink**) 06-07-07, 07:05

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:

Quote:

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:

Quote:

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

r937 · #2 (**permalink**) 06-07-07, 08:07

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