select distinct best row based on a condition

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 2008 Forums

Transact-SQL (2008)

select distinct best row based on a condition

Author

Topic

psikadelik
Starting Member

5 Posts

Posted - 2010-09-08 : 13:04:33

Hi all .

The application I'm working on only allowed that one user had one usergroup. I create a User_UserGroup table to allow a user to have n usergroups.

My problem now is the following:

User 'ablanco' belongs to UserGroup 'comercial' and 'financeira'

If i execute this commands i get the following results:

-----------------------------------------------------------------------------------

SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'comercial'

http://img26.imageshack.us/i/49234843.png/

-----------------------------------------------------------------------------------

SELECT * FROM [T_APP_ModulePermition] WHERE [UserGroup] = 'financeira'

http://img259.imageshack.us/i/32526938.png/

-----------------------------------------------------------------------------------

I now need to get distinct rows that belong to ablanco's Usergroups but if on one group the module is Enable=0 and on the other the same module is Enable=1 it should return the ont that has Enable = 1

The attempt i made without taking in consideration this 'Enable' part was the following:

SELECT MP.* FROM [T_APP_ModulePermition] MP
INNER JOIN T_APP_UserGroup_Detail UGD ON MP.UserGroup=UGD.UserGroup
INNER JOIN T_APP_User U ON UGD.UserId = U.UserId
WHERE U.UserId='ablanco'

But since i cant use DISTINCT.* i get all the 14 rows.

If someone can help me on this i would be thankful.
[url][/url]

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-09 : 08:11:50

Can u provide sm test data along with table designs

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 01:13:49

What should be your desired o/p ?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

CSears
Starting Member

39 Posts

Posted - 2010-09-10 : 23:56:50

[code]
SELECT MP.ModuleCode, CASE SUM(MP.Enabled) WHEN 0 THEN 0 ELSE 1 END AS [Enabled] FROM [T_APP_ModulePermition] MP
INNER JOIN T_APP_UserGroup_Detail UGD ON MP.UserGroup=UGD.UserGroup
INNER JOIN T_APP_User U ON UGD.UserId = U.UserId
WHERE U.UserId='ablanco'
GROUP BY MP.ModuleCode
[/code]

This will show enabled if the user is a member of any Usergroup that has that Module enabled, but will show 0 if all UserGroups has that the user is assigned to have the module are disabled. Was that something like what you were looking for?

CSears
Starting Member

39 Posts

Posted - 2010-09-11 : 00:04:55

[code]SELECT MP.ModuleCode, MAX(MP.Enabled) AS [Enabled] FROM [T_APP_ModulePermition] MP
INNER JOIN T_APP_UserGroup_Detail UGD ON MP.UserGroup=UGD.UserGroup
INNER JOIN T_APP_User U ON UGD.UserId = U.UserId
WHERE U.UserId='ablanco'
GROUP BY MP.ModuleCode[/code]

Even easier!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources