CODEWITHSUNDEEP
sqlsql-server
user1587872
user1587872

Reputation: 365

SQL query to fetch output based on multiple row values

I have table with sample data as follows 

-----------------------------
UserID   | ScreenID | rights |
------------------------------
 admin        22         0
 admin        23         0
 admin        24         0

 userA        22         1
 userA        23         1
 userA        24         1

 userB        22         0
 userB        23         0
 userB        24         1

Now I want to fetch 'UserID' and 'rights' details as follows: For each user if the "rights" for all the ScreenIDs are same then that rights value (either '0' or '1') else rights value as 2

That means, required output from the above table should be:

admin , 0
userA , 1
userB  ,2

Please help me in writing SQL query for the same. Thanks.

Upvotes: 1

Views: 66

Answers (2)

StuartLC
StuartLC

Reputation: 107387

With a cte or derived table which does a GROUP BY on UserId, you can then tell whether all Rights are the same: 

SELECT UserID,
       CASE 
          WHEN minR = maxR THEN minR
          ELSE 2
       END AS Result
FROM
(
  SELECT UserID, MIN(Rights) as minR, MAX(Rights) as maxR
  FROM UserRights
  GROUP BY UserID
) x;

SqlFiddle here

Upvotes: 5

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

Something like (untested on sqlserver):

select userid
     , case when min(rights) = max(rights) 
            then min(rights) 
            else 2 
       end 
from t 
group by userid

Upvotes: 2

Related Questions