December 31, 2007 at 1:30 pm
Need some join help...
Table 1 - Modules:
ID | Name
1 | A
2 | B
3 | C
Table 2 - CompanyModules
ModuleID | CompanyID
1 | 1
2 | 1
3 | 1
1 | 2
I'd like to return the following result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | True
1 | B | True
1 | C | True
2 | A | True
2 | B | False
2 | C | False
What would be the query for this? Thanks.
This is the query I have tried:
select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from
CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID
group By CompanyModules.CompanyID, Modules.Name
Order by CompanyID
However, it only returns a partial result set:
CompanyModules.CompanyID | Modules.Name | Present
1 | A | 1
1 | B | 1
1 | C | 1
2 | A | 1
December 31, 2007 at 1:33 pm
How do you get the data in the third column? It doesn't make sense from your data example.
December 31, 2007 at 1:53 pm
The third column is just the indicator (yes/no) on whether that Company has that module. I thought of using count() to get a 0 or 1 but it doesn't work as you can see.
December 31, 2007 at 2:30 pm
Ah, I see.
The problem is that you have matches and you're really looking for some sort of cross join that displays all possibilities. Then you need to somehow join that back to the original list.
Are you trying to show some sort of possibilities or checkbox list?
December 31, 2007 at 2:39 pm
I don't see a good way to do this, even with a cross join. You'll get more cross join stuff out of there.
If this is of any size, you'll run into performance problems. I'd suggest that you work with the client side and handle the display there. Maybe loop through the various companies, and apply the modules as an inner loop.
select id 'module', moduleid 'moduleid', companyid, name
from modules cross join companymodules
December 31, 2007 at 2:44 pm
That seems to have done the trick... I had to bring in the companies table to do a cross join as you said:
SELECT Companies.ID AS CID, Modules.ID AS MID, CASE WHEN Companies.ID = a.CompanyID AND
Modules.ID = a.ModuleID THEN 1 ELSE 0 END AS Present
FROM Companies CROSS JOIN
Modules LEFT OUTER JOIN
(SELECT ModuleID, CompanyID
FROM CompanyModules) AS a ON Companies.ID = a.CompanyID AND Modules.ID = a.ModuleID
I'll keep the performance issue in mind. right now we are looking at 10 modules and a 1000 clients (total 10000 rows?), which I presume is ok for our server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply