SQL Query

jverasql
jverasql used Ask the Experts™
on
Hello,

I have a table with many columns. I need to query three of the fields siteid, roleid and userid. For each siteid, there may be multiple userids. If there is a userid there is one roleid. For two users, I am trying to compare the siteid there have access to and their role. I want one row for each site. On each row I want to know if the each of the users has access and their role.  If they do not have access to either siteid, I do not want that siteid to appear in the results.

Thank you,
John
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
Make a sample data like below you have
siteid roleid userid
1           11         111
1           12         112
1           null  

then also give sample result you want to have based on the above table.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
any chance you have some sample data.

Author

Commented:
Site User Role
1       1         50
1      2         75
1      3          60
2      2         25
2      3          75
3      1         50
3      3          60
4      3          75

Result for users 1 and 2
Site User1 Role1 User 2 Role2
1       1         50         2         75
2      1         Null       2         25
3      1         50          2         Null
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

You may try following query:
DECLARE @User1 int
SET @User1 = <User1IDvalue>
DECLARE @User2 int
SET @User2 = <User2IDvalue>

SELECT SiteID, 
       @User1 User1, MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1, 
       @User2 User2, MAX(CASE WHEN UserID = @User2 THEN RoleID ELSE NULL END) Role2
  FROM YourTable
 GROUP BY SiteID, User1, User2
 ORDER BY SiteID

Open in new window

Yo have to provide initial values for @UserX variables.
Mike EghtebasDatabase and Application Developer

Commented:
I am getting:
Siteid UserA   RoleA   UserB   RoleB
1	1	50	2	75
2	NULL	NULL	2	25
3	1	50	NULL	NULL
4	NULL	NULL	NULL	NULL

Open in new window

using (replace #t with your table name):
Declare @userA int = 1;
Declare @userB int = 2;

;with d 
As
(
Select Siteid from #t
Group By Siteid 
)
Select d.Siteid
     , a.userid as UserA, a.roleid as RoleA
     , b.userid as UserB, b.roleid as RoleB
From d 
Outer APPLY (SELECT userid, roleid
From #t t WHERE d.Siteid = t.Siteid
              and userid = @userA) as a
Outer APPLY (SELECT userid, roleid
From #t t WHERE d.Siteid = t.Siteid
              and userid = @userB) as b
Order by siteid

Open in new window

With temp table #t:
create table #t(siteid int,  userid int,  roleid int);
insert into #t(siteid, userid, roleid) values
 (1, 1, 50)
,(1, 2, 75)
,(1, 3, 60)
,(2, 2, 25)
,(2, 3, 75)
,(3, 1, 50)
,(3, 3, 60)
,(4, 3, 75);
select * from #t;

Open in new window

I think, you may be able to work with 3 column output like:
Siteid  RoleA   RoleB
1	50	75
2	NULL	25
3	50	NULL
4	NULL	NULL

Open in new window

     
Why to repeat users A and B number because the column names RoleA and RoleB identifies them clearly.

Testing for users 1 and 3 for five column output With:
Declare @userA int = 1;
Declare @userB int = 3;

I am getting:
Siteid UserA   RoleA   UserB   RoleB
1	1	50	3	60
2	NULL	NULL	3	75
3	1	50	3	60
4	NULL	NULL	3	75

Open in new window


Mike
Senior Consultant
Commented:
Using the sample table provided in the previous comment,  the following select statement will give you the desired output -

DECLARE @User1 int
SET @User1 = 1
DECLARE @User2 int
SET @User2 = 2


SELECT * 
FROM 
( 
SELECT SiteID, 
       @User1 User1, MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1, 
       @User2 User2, MAX(CASE WHEN UserID = @User2 THEN RoleID ELSE NULL END) Role2
  FROM #t 
 GROUP BY SiteID
  )  TempTab  

WHERE NOT ( Role1 IS NULL AND Role2 IS NULL ) 
ORDER BY SiteID

Open in new window


Replace the table name #t with your table name.
Mike EghtebasDatabase and Application Developer

Commented:
Hi Anoo,
I clicked on Good Comment for your comment because it is so simple without using CTE, etc.

Is there a way to have variables replace the alias names?

For example:    
@User1 'UserId_' + @User1 , MAX(CASE WHEN UserID = @User1 THEN RoleID ELSE NULL END) Role1,

Open in new window


I suppose we have to store it in @sql first and then execute it. It will be nice to make work that way because then we don't have to revise tsql for different users.

----

@pcelba,

When I tested your solution, it didn't work. Now I see User1, User2 in the below line had to be removed.

GROUP BY SiteID --, User1, User2

Author

Commented:
Thank you for all the feedback. I will try in the morning.  Questions.

Is there a way to do this without variables?
I do not have much experience with variables in SQL.  How would I have known variables would have been needed for this?
I understand the max function, but not completely sure why it is needed here. Please explain.

Thanks again.
Mike EghtebasDatabase and Application Developer

Commented:
Using the solution from Anoo, to compare 1 and 2:
SELECT * 
FROM 
( 
SELECT SiteID, 
       1 User1, MAX(CASE WHEN UserID = 1 THEN RoleID ELSE NULL END) Role1, 
       2 User2, MAX(CASE WHEN UserID = 2 THEN RoleID ELSE NULL END) Role2
  FROM #t 
 GROUP BY SiteID
  )  TempTab  

WHERE NOT ( Role1 IS NULL AND Role2 IS NULL ) 
ORDER BY SiteID

Open in new window


or to compare 1 and 3:
SELECT * 
FROM 
( 
SELECT SiteID, 
       1 User1, MAX(CASE WHEN UserID = 1 THEN RoleID ELSE NULL END) Role1, 
       3 User3, MAX(CASE WHEN UserID = 3 THEN RoleID ELSE NULL END) Role3
  FROM #t 
 GROUP BY SiteID
  )  TempTab  

WHERE NOT ( Role1 IS NULL AND Role3 IS NULL ) 
ORDER BY SiteID;

select * from #t
order by userid;

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
jverasql, do you still need help with this question?

Author

Commented:
Thank you. Apologize for the delay in response.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial