May 20, 2005 at 1:17 pm
Hi All
Is any body can help me out to solve this following T-SQL please...
T-SQL:
SELECT a.name as UserName, b.name as Rolename FROM sysusers a,
(SELECT name FROM sysusers b
WHERE (b.name NOT LIKE 'db%')
AND (b.name NOT IN ('guest', 'public'))
AND (b.uid = gid)) b
WHERE (a.name NOT LIKE 'db%')
AND (a.name NOT IN ('guest', 'public'))
AND (a.islogin = 1)
Results:
Users | Roles |
sonny | Admin |
williams | Admin |
sonny | ExecuteObjects |
williams | ExecuteObjects |
but i want single entry like this:
Users | Roles |
sonny | Admin |
williams | ExecuteObjects |
Thanx in advance
SqlIndia
May 20, 2005 at 1:49 pm
SELECT distinct a.name as UserName, b.name as Rolename FROM sysusers a,
(SELECT name FROM sysusers b
WHERE (b.name NOT LIKE 'db%')
AND (b.name NOT IN ('guest', 'public'))
AND (b.uid = gid)) b
WHERE (a.name NOT LIKE 'db%')
AND (a.name NOT IN ('guest', 'public'))
AND (a.islogin = 1)
??
* Noel
May 20, 2005 at 4:30 pm
Hi noeld
thnx for reply.. but distinct will not slove the problem. I have tried before posted it. I also have tried group and having clause.. but no luck.. u see if two users and two roles are created in database then my query show four rows rather i want to display two rows, one for each...users and roles....
could anyonehelp ?????
Regards
SqlIndia
May 20, 2005 at 4:37 pm
I think you haven't clearly stated the problem or it is ambiguous by its nature. Given your example, can you state exaclty what logic you would like to be applied to select the desired rows.
For example when the same user is in two different roles how is it decided which role is to be displayed?
May 22, 2005 at 9:25 am
Hi ron k
Here it is explanation...
I try to get following results
1. List of users exists in databse... ( can be found from sysusers table)
2. List of Roles exist in database... (can be found sysusers table)
3. List users who are assign to specific roles..( can be done using sp_helprolemember or sysusers and sysmembers tables)
But I want result 1 and 2 by using one single query...to display two columns like
username, rolename...(just like table in previous section of this post)...
SqlIndia
May 22, 2005 at 10:48 am
sqlindia - I don't think you've addressed ron's question...which of the 2 or many roles would you want displayed ?
**ASCII stupid question, get a stupid ANSI !!!**
May 22, 2005 at 1:37 pm
Why don't you just use the solution they gave you two days ago at sqlTeam??
May 22, 2005 at 11:32 pm
I think it's a really bad way of doing it, because it doesn't seem right to return the data that way. Much better to return two sets of data, which you then handle in the client.
But another option is to use some sort of ranking function, which goes at least as high as your longest list. Apply the ranking function to each list of data, putting those queries into sub-queries which you join against.
For example, using an equivalent to row_number(), you could do something like:
select a.name, b.name from
(SELECT name, row_number() rnk FROM sysusers a
WHERE (a.name NOT LIKE 'db%')
AND (a.name NOT IN ('guest', 'public'))
AND (a.islogin = 1)) a
left join
(SELECT name, row_number() rnk FROM sysusers b
WHERE (b.name NOT LIKE 'db%')
AND (b.name NOT IN ('guest', 'public'))
AND (b.uid = gid)) b
on a.rnk = b.rnk
but if you have more entries in 'b' than in 'a', then you would want to do a 'right join' instead. But honestly, don't do it that way... you could try:
SELECT name, 'role' nametype FROM sysusers b
WHERE (b.name NOT LIKE 'db%')
AND (b.name NOT IN ('guest', 'public'))
AND (b.uid = gid)
union all
select name, 'user' nametype from sysusers a
WHERE (a.name NOT LIKE 'db%')
AND (a.name NOT IN ('guest', 'public'))
AND (a.islogin = 1)
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply