to get users which have more than 5 roles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

to get users which have more than 5 roles

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-03-21 : 09:59:00

I am trying to get all the users which have more than 5 roles assigned and there roleid is greater than equal to 5. With this query i can get all the users where roleid is greater than 5. I am not able to get users which have more than 5 roles assigned.

SELECT MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END) AS FirstName,
MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END) AS LastName,
MAX(CASE WHEN propertyName = 'Company Name' THEN propertyValue END) AS CompanyName,
MAX(CASE WHEN propertyName = 'City' THEN propertyValue END) AS City, MAX(CASE WHEN propertyName = 'State' THEN propertyValue END)
AS State, MAX(CASE WHEN propertyName = 'Country' THEN propertyValue END) AS Country,
MAX(CASE WHEN propertyName = 'Postalcode' THEN propertyValue END) AS Postalcode,
MAX(CASE WHEN propertyName = 'telephone' THEN propertyValue END) AS Phone, UserRoles.RoleID, Roles.RoleName, Users.Email
FROM (SELECT DISTINCT PropertyName, PropertyValue, UserID
FROM ProfilePropertyDefinition AS ppd INNER JOIN
UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID) d INNER JOIN
UserRoles ON d.UserID = UserRoles.UserID INNER JOIN
Users ON UserRoles.UserID = Users.UserID INNER JOIN
Roles ON UserRoles.RoleID = Roles.RoleID
WHERE (UserRoles.RoleID > 5)
GROUP BY d.UserID, UserRoles.RoleID, Users.Email, Roles.RoleName

ORDER BY Users.firstname

Here are the table defs

CREATE TABLE [Roles] (
[RoleID] [int] IDENTITY (0, 1) NOT NULL ,
[PortalID] [int] NOT NULL ,
[RoleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ServiceFee] [money] NULL CONSTRAINT [DF_Roles_ServiceFee] DEFAULT (0),
[BillingFrequency] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TrialPeriod] [int] NULL ,
[TrialFrequency] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BillingPeriod] [int] NULL ,
[TrialFee] [money] NULL ,
[IsPublic] [bit] NOT NULL CONSTRAINT [DF_Roles_IsPublic] DEFAULT (0),
[AutoAssignment] [bit] NOT NULL CONSTRAINT [DF_Roles_AutoAssignment] DEFAULT (0),
[RoleGroupID] [int] NULL ,
[RSVPCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IconFile] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Roles] PRIMARY KEY NONCLUSTERED
(
[RoleID]
) ON [PRIMARY] ,
CONSTRAINT [IX_RoleName] UNIQUE NONCLUSTERED
(
[PortalID],
[RoleName]
) ON [PRIMARY] ,
CONSTRAINT [FK_Roles_Portals] FOREIGN KEY
(
[PortalID]
) REFERENCES [Portals] (
[PortalID]
) ON DELETE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_Roles_RoleGroups] FOREIGN KEY
(
[RoleGroupID]
) REFERENCES [RoleGroups] (
[RoleGroupID]
)
) ON [PRIMARY]
GO

CREATE TABLE [UserRoles] (
[UserRoleID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[RoleID] [int] NOT NULL ,
[ExpiryDate] [datetime] NULL ,
[IsTrialUsed] [bit] NULL ,
[EffectiveDate] [datetime] NULL ,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRoleID]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserRoles_Roles] FOREIGN KEY
(
[RoleID]
) REFERENCES [Roles] (
[RoleID]
) ON DELETE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_UserRoles_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [Users] (
[UserID]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO

CREATE TABLE [Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsSuperUser] [bit] NOT NULL CONSTRAINT [DF_Users_IsSuperUser] DEFAULT (0),
[AffiliateId] [int] NULL ,
[Email] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DisplayName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Users_DisplayName] DEFAULT (''),
[UpdatePassword] [bit] NOT NULL CONSTRAINT [DF_Users_UpdatePassword] DEFAULT (0),
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Users] UNIQUE NONCLUSTERED
(
[Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [ProfilePropertyDefinition] (
[PropertyDefinitionID] [int] IDENTITY (1, 1) NOT NULL ,
[PortalID] [int] NULL ,
[ModuleDefID] [int] NULL ,
[Deleted] [bit] NOT NULL ,
[DataType] [int] NOT NULL ,
[DefaultValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyCategory] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PropertyName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Length] [int] NOT NULL CONSTRAINT [DF_ProfilePropertyDefinition_Length] DEFAULT (0),
[Required] [bit] NOT NULL ,
[ValidationExpression] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ViewOrder] [int] NOT NULL ,
[Visible] [bit] NOT NULL ,
CONSTRAINT [PK_ProfilePropertyDefinition] PRIMARY KEY CLUSTERED
(
[PropertyDefinitionID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ProfilePropertyDefinition_Portals] FOREIGN KEY
(
[PortalID]
) REFERENCES [Portals] (
[PortalID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 10:18:39

Something like this

SELECT		MAX(CASE WHEN d.PropertyName = 'FirstName' THEN p.PropertyValue END) AS FirstName, 
		MAX(CASE WHEN d.PropertyName = 'LastName' THEN p.PropertyValue END) AS LastName, 
		MAX(CASE WHEN d.PropertyName = 'Company Name' THEN p.PropertyValue END) AS CompanyName, 
		MAX(CASE WHEN d.PropertyName = 'City' THEN p.PropertyValue END) AS City,
		MAX(CASE WHEN d.PropertyName = 'State' THEN p.PropertyValue END) AS State,
		MAX(CASE WHEN d.PropertyName = 'Country' THEN p.PropertyValue END) AS Country, 
		MAX(CASE WHEN d.PropertyName = 'Postalcode' THEN p.PropertyValue END) AS Postalcode, 
		MAX(CASE WHEN d.PropertyName = 'Telephone' THEN p.PropertyValue END) AS Phone,
		ur.RoleID,
		r.RoleName,
		u.Email
FROM		ProfilePropertyDefinition AS d
INNER JOIN	UserProfile AS p ON p.PropertyDefinitionID = d.PropertyDefinitionID
INNER JOIN	UserRoles AS ur ON ur.UserID = d.UserID
INNER JOIN	Users AS u ON Users.UserID = ur.UserID
INNER JOIN	Roles AS r ON Roles.RoleID = ur.RoleID
INNER JOIN	(
			SELECT		UserID,
					COUNT(DISTINCT RoleID) AS cnt
			FROM		UserRoles
			WHERE		RoleID > 5
			GROUP BY	UserID
		) AS x ON x.UserID = u.UserID
WHERE		ur.RoleID > 5
		AND x.cnt > 5
GROUP BY	d.UserID,
		ur.RoleID,
		u.Email,
		r.RoleName
ORDER BY	u.FirstName

Peter Larsson
Helsingborg, Sweden

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-03-21 : 11:47:31

i am geting this error. I know this sql is complicated. I am k with sql. I am more of a web guy than a sql guy.
Invalid column name 'UserID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'PropertyValue'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'UserID'.

SELECT MAX(CASE WHEN d.PropertyName = 'FirstName' THEN d.PropertyValue END) AS FirstName,
MAX(CASE WHEN d.PropertyName = 'LastName' THEN d.PropertyValue END) AS LastName,
MAX(CASE WHEN d.PropertyName = 'Company Name' THEN d.PropertyValue END) AS CompanyName,
MAX(CASE WHEN d.PropertyName = 'City' THEN d.PropertyValue END) AS City, MAX(CASE WHEN d.PropertyName = 'State' THEN d.PropertyValue END)
AS State, MAX(CASE WHEN d.PropertyName = 'Country' THEN d.PropertyValue END) AS Country,
MAX(CASE WHEN d.PropertyName = 'Postalcode' THEN d.PropertyValue END) AS Postalcode,
MAX(CASE WHEN d.PropertyName = 'Telephone' THEN d.PropertyValue END) AS Phone, ur.RoleID, r.RoleName, u.Email
FROM ProfilePropertyDefinition d INNER JOIN
UserProfile p ON p.PropertyDefinitionID = d.PropertyDefinitionID INNER JOIN
UserRoles ur ON ur.UserID = d.UserID INNER JOIN
Users u ON u.UserID = ur.UserID INNER JOIN
Roles r ON r.RoleID = ur.RoleID INNER JOIN
(SELECT UserID, COUNT(DISTINCT RoleID) AS cnt
FROM UserRoles
WHERE RoleID > 5
GROUP BY UserID) x ON x.UserID = u.UserID
WHERE (ur.RoleID > 5) AND (x.cnt > 5)
GROUP BY d.UserID, ur.RoleID, u.Email, r.RoleName
ORDER BY u.FirstName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 12:29:05

Where do Propertyvalue column reside?
There isn't one the definitions you posted...

Peter Larsson
Helsingborg, Sweden

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-03-21 : 12:33:36

here is the property value

CREATE TABLE [UserProfile] (
[ProfileID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NOT NULL ,
[PropertyDefinitionID] [int] NOT NULL ,
[PropertyValue] [nvarchar] (3750) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PropertyText] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Visibility] [int] NOT NULL CONSTRAINT [DF__UserProfi__Visib__0D99FE17] DEFAULT (0),
[LastUpdatedDate] [datetime] NOT NULL ,
CONSTRAINT [PK_UserProfile] PRIMARY KEY NONCLUSTERED
(
[ProfileID]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserProfile_ProfilePropertyDefinition] FOREIGN KEY
(
[PropertyDefinitionID]
) REFERENCES [ProfilePropertyDefinition] (
[PropertyDefinitionID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_UserProfile_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [Users] (
[UserID]
) ON DELETE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 16:37:27

Answer update above...

Peter Larsson
Helsingborg, Sweden

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-03-22 : 09:28:08

Well i am still getting the same error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'UserID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'UserID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'UserID'.
Server: Msg 207, Level 16, State 1, Line 1

SELECT MAX(CASE WHEN d.PropertyName = 'FirstName' THEN p.PropertyValue END) AS FirstName,
MAX(CASE WHEN d.PropertyName = 'LastName' THEN p.PropertyValue END) AS LastName,
MAX(CASE WHEN d.PropertyName = 'Company Name' THEN p.PropertyValue END) AS CompanyName,
MAX(CASE WHEN d.PropertyName = 'City' THEN p.PropertyValue END) AS City, MAX(CASE WHEN d.PropertyName = 'State' THEN p.PropertyValue END)
AS State, MAX(CASE WHEN d.PropertyName = 'Country' THEN p.PropertyValue END) AS Country,
MAX(CASE WHEN d.PropertyName = 'Postalcode' THEN p.PropertyValue END) AS Postalcode,
MAX(CASE WHEN d.PropertyName = 'Telephone' THEN p.PropertyValue END) AS Phone, ur.RoleID, r.RoleName, u.Email
FROM ProfilePropertyDefinition d INNER JOIN
UserProfile p ON p.PropertyDefinitionID = d.PropertyDefinitionID INNER JOIN
UserRoles ur ON ur.UserID = d.UserID INNER JOIN
Users u ON u.UserID = ur.UserID INNER JOIN
Roles r ON r.RoleID = ur.RoleID INNER JOIN
(SELECT UserID, COUNT(DISTINCT RoleID) AS cnt
FROM UserRoles
WHERE RoleID > 5
GROUP BY UserID) x ON x.UserID = u.UserID
WHERE (ur.RoleID > 5) AND (x.cnt > 5)
GROUP BY d.UserID, ur.RoleID, u.Email, r.RoleName
ORDER BY u.FirstName

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-22 : 10:21:56

I highly recommend doing this in three simple, short steps, perhaps using views to help you out. You are trying to do too much at once, all in 1 sql statement.

step 1:

First, get a list of userID's that are in 5 roles or more. Don't worry about formatting, cross-tabbing, getting their last name, or anything else -- just focus on DATA initially. Based on what you've been trying to do, that SQL would look something like this:


SELECT 
  UserID, COUNT(DISTINCT RoleID) AS cnt
FROM 
  UserRoles
WHERE 
  RoleID > 5
GROUP BY 
  UserID

Now, looking at your UserRoles table definition, I see that there are columns like Effective date, Expiry Date, and a few others. Are those columns important to your results? Do you need to ensure that the user/role assignment is valid for a particular date or some other circumstance? If so, then include that into the above sql, and work on it as is, keeping it simple and small and don't worry about the other stuff just yet. Make sure that you can get a distinct list of valid UserID's that fit your criteria.

step 2:

Now, ONLY WHEN YOU HAVE COMPLETED THAT FIRST STEP, you can now think about returning the results you need. Since your database design is -- um, well, let's just say it's "overly-flexible" to be nice -- it takes more work than normal to return things like user name and all that. So, put the above stuff aside for a few minutes, and let's focus on a SEPARATE sql statement to do just that -- return all UserID's and their data. While we are at it, let's do some formatting and indenting to actually make this stuff easy to work with:


SELECT u.UserID,
 MAX(CASE WHEN d.PropertyName = 'FirstName' THEN p.PropertyValue END) AS FirstName,
 MAX(CASE WHEN d.PropertyName = 'LastName' THEN p.PropertyValue END) AS LastName,
 MAX(CASE WHEN d.PropertyName = 'Company Name' THEN p.PropertyValue END) AS CompanyName,
 MAX(CASE WHEN d.PropertyName = 'City' THEN p.PropertyValue END) AS City, 
 MAX(CASE WHEN d.PropertyName = 'State' THEN p.PropertyValue END) AS State, 
 MAX(CASE WHEN d.PropertyName = 'Country' THEN p.PropertyValue END) AS Country,
 MAX(CASE WHEN d.PropertyName = 'Postalcode' THEN p.PropertyValue END) AS Postalcode,
 MAX(CASE WHEN d.PropertyName = 'Telephone' THEN p.PropertyValue END) AS Phone, 
 u.Email
FROM 
  ProfilePropertyDefinition d 
INNER JOIN
  UserProfile p ON p.PropertyDefinitionID = d.PropertyDefinitionID 
INNER JOIN
  Users u ON u.UserID = d.UserID
GROUP BY 
  u.UserID, u.Email

Again, don't worry about the roles just yet -- focus on writing this separate sql statement, make sure it works, add more columns as needed, test it, and make sure you feel good about it. The goal is to make sure you return 1 row per UserID with all of the info necessary pertaining to that user, ignoring roles and all that for now. This would probably be a good SELECT to save as a VIEW, so you don't need to write it over and over:


CREATE View UserDetails
AS
 ... above SQL goes here ...

Once that view is created, anywhere else in your database, you can just write:

SELECT * FROM UserDetails

or you can join to the UserDetails view, or whatever else you can do to any other table.

step 3:

OK, so now we have two separate things working -- a SELECT that returns the users in more than 5 roles, and a nice View that we can use over and over as many times as needed that returns User info.

So, all we need to do now is just join the two, and we are done:


SELECT u.*
FROM UserDetails u
INNER JOIN
  ( .. the sql from step #1 that returns 1 row per UserID in more than 5 roles ...) r
ON 
  u.userID = r.UserID

And ... that's it!

Break it into smaller pieces, take it one step at a time, and then when each piece is working correctly, you simply join them all together. While doing this, any pieces that might be re-usable, you can save as Views so that you can simple reference the View whenever you need it.

Every SELECT that you ever write should be done in this manner, especially if they are even slightly complicated. Too many people just keep stuffing JOINS into 1 big SELECT and randomly start grouping and adding criteria until things start to look "ok" -- don't do this! Break things down into steps, solve each one individually.

I hope this helps.

- Jeff
http://weblogs.sqlteam.com/JeffS

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-03-22 : 11:25:18

Thanks for all of ur help. i got the query to work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 11:29:53

[code]SELECT MAX(CASE WHEN d.PropertyName = 'FirstName' THEN p.PropertyValue END) AS FirstName,
MAX(CASE WHEN d.PropertyName = 'LastName' THEN p.PropertyValue END) AS LastName,
MAX(CASE WHEN d.PropertyName = 'Company Name' THEN p.PropertyValue END) AS CompanyName,
MAX(CASE WHEN d.PropertyName = 'City' THEN p.PropertyValue END) AS City,
MAX(CASE WHEN d.PropertyName = 'State' THEN p.PropertyValue END) AS State,
MAX(CASE WHEN d.PropertyName = 'Country' THEN p.PropertyValue END) AS Country,
MAX(CASE WHEN d.PropertyName = 'Postalcode' THEN p.PropertyValue END) AS Postalcode,
MAX(CASE WHEN d.PropertyName = 'Telephone' THEN p.PropertyValue END) AS Phone,
ur.RoleID,
r.RoleName,
u.Email
FROM ProfilePropertyDefinition AS d
INNER JOIN UserProfile AS p ON p.PropertyDefinitionID = d.PropertyDefinitionID
INNER JOIN UserRoles AS ur ON ur.UserID = p.UserID
INNER JOIN Users AS u ON Users.UserID = ur.UserID
INNER JOIN Roles AS r ON Roles.RoleID = ur.RoleID
INNER JOIN (
SELECT UserID,
COUNT(DISTINCT RoleID) AS cnt
FROM UserRoles
WHERE RoleID > 5
GROUP BY UserID
) AS x ON x.UserID = u.UserID
WHERE ur.RoleID > 5
AND x.cnt > 5
GROUP BY d.UserID,
ur.RoleID,
u.Email,
r.RoleName
ORDER BY u.FirstName[/code]

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-22 : 14:34:59

i just noticed I missed the "show role Id's greater than 5" requirement ... very easy to add, same logic applies, that just becomes step 4 in the process.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources