select distinct best row based on a condition
-
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] MPINNER 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.
-
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
I'm a bit confused by the post.
Can you please post the expected results, and what you get with your current query?
edit: whoops, had this open for a while before I replied and Wayne beat me to it
-
Sorry for not had post all that info.
What i want to accomplish is this:
SELECT X.* FROM
(
SELECT MP.*, RNum=ROW_NUMBER() OVER (PARTITION BY MP.ModuleCode ORDER BY MP.UserGroup)
FROM [T_APP_ModulePermition] MP
JOIN T_APP_UserGroup_Detail UGD ON MP.UserGroup=UGD.UserGroup
WHERE UGD.UserID='ablanco' AND MP.Enabled=1
) AS X
WHERE RNum=1
-
You still didn't post the expected results.
Just free-type the results you are hoping to get, and we can write a query to get them for you.
-
jpdlp (9/8/2010)
Hi all .The attempt i made without taking in consideration this 'Enable' part was the following:
SELECT MP.* FROM [T_APP_ModulePermition] MPINNER 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'
Why not use an extra condition in the WHERE clause with MP.Enabled = 1 ?
SELECT MP.* FROM [T_APP_ModulePermition] MPINNER 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' AND MP.Enabled = 1
(The bold ones are what i have added)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply