E n u m (Resolved) - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

E n u m (Resolved)

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 11:07:11

Enumeration:
I have 2 tables GPM and Gusr

GPM ( gName, Mem , id)
Gusr (name,sid) -- sid can be used to join with id

under Gpm data is as below


gname    mem    id
-------------------
admin    admin  1
admin    dom    2
oper     ''     3
Guest    Guest  4
lg       oper   6
usr      db1    7
usr      db2    8
nw opr   ''     
tcnt     ''


name     id    
--------------
admin    1
guest    4

For each domain in scope, I need to obtain the members of the foll groups

Local Admin
Acct Operators
Server Operators

The output table to show the Administrator groups and their member accounts ( enumerated)

e.g. Under admin if i have G1G2 G3 groups
Admin --> G1
Admin --> G2
Admin --> G3

and under G1 if i have M1 M2..
G1-->M1,M2
G2-->M3,M1
..
..

I want all the members to be displayed as
Admin->G1->M1
Admin->G1->M2
Admin->G2->M3
Admin->G2->M1
..

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 11:12:36

sorry not enough info

where's the children relationship stored?
i cant see any table where you're defined children within a group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 11:26:00

gname - has all the parent info and mem column has the child info, also need to get the members from Gusr

basically all the members along with its parent

some more data


name     id    
--------------
admin    1
guest    4
dc       9

gname    mem    id
-------------------
admin    admin  1
admin    dom    2
oper     ''     3
Guest    Guest  4
lg       oper   6
usr      db1    7
usr      db2    8
nw opr   ''     
tcnt     ''    
usr      dc     9
dom      at     10
dc       sy


admin->dom
dom->  at
usr->dc->sy

need to get all the members

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 11:41:47

[code]
;With CTE
AS
(

SELECT g.gname,g.mem,CAST(mem AS varchar(max)) AS [path]
from GPM g
INNER JOIN GUsr u
ON g.id = u.sid
UNION ALL
SELECT g.gname,g.mem,CAST(c.[path] + '->' + g.mem AS varchar(max))
FROM CTE c
INNER JOIN GPM g
ON g.gname = c.mem
WHERE g.gname <> g.mem
)

SELECT g.gname,[path]
FROM CTE
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 13:53:58

Thanks Vishak, I am getting result but the below path is not coming w

->

CAST(c.[path] + '->' + g.mem AS varchar(max))


;With CTE
AS
(

SELECT g.gname,g.mem,CAST(mem AS varchar(max)) AS [path]
from GPM g
INNER JOIN  GUsr u
ON g.id = u.sid
UNION ALL
SELECT g.gname,g.mem,CAST(c.[path] + '->' + g.mem AS varchar(max))
FROM CTE c
INNER JOIN GPM g
ON g.gname = c.mem
WHERE g.gname <> g.mem
)

SELECT gname,[path]
FROM CTE

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 14:53:51

its working fine for me


declare @GUsr table
 (name varchar(100),
 id int)

INSERT INTO @GUsr (name,id)
VALUES('admin',1),
('guest',4),
('dc',9)

declare @GPM table
(
gname varchar(100),    
mem varchar(100),    
id int
)
INSERT INTO @GPM (gname,    mem,    id)
VALUES('admin','admin',1),
('admin','dom',2),
('oper','''',3),
('Guest','Guest',4),
('lg','oper',6),
('usr','db1',7),
('usr','db2',8),
('nw opr','',''),
('tcnt','',''),
('usr','dc',9),
('dom','at',10),
('dc','sy','')


;With CTE
AS
(

SELECT g.gname,g.mem,CAST(mem AS varchar(max)) AS [path]
from @GPM g
INNER JOIN  @GUsr u
ON g.id = u.id
UNION ALL
SELECT g.gname,g.mem,CAST(c.[path] + '->' + g.mem AS varchar(max))
FROM CTE c
INNER JOIN @GPM g
ON g.gname = c.mem
WHERE g.gname <> g.mem
)

SELECT *
FROM CTE


output
----------------------------------------
gname	mem	path
----------------------------------------
admin	admin	admin
Guest	Guest	Guest
usr	dc	dc
dc	sy	dc->sy
admin	dom	admin->dom
dom	at	admin->dom->at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 14:54:45

Vishak,

any problem with the above query, why I am not getting data from below column I am getting plain result

CAST(c.[path] + '->' + g.mem AS varchar(max))

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 14:59:07

quote:
Originally posted by aakcse

Vishak,

any problem with the above query, why I am not getting data from below column I am getting plain result

CAST(c.[path] + '->' + g.mem AS varchar(max))

-Neil

i dont know what you're doing wrong as I'm getting it properly (see previous post for illustration)
Please let me know if you've some other rules also to be taken care of

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 15:12:34

Thanks after adding one more level to actual data I am getting it.

cant we get data like below in path column as dc is a member of usr group
"usr->dc"

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:18:06

ok...here you go


declare @GUsr table
 (name varchar(100),
 id int)

INSERT INTO @GUsr (name,id)
VALUES('admin',1),
('guest',4),
('dc',9)

declare @GPM table
(
gname varchar(100),    
mem varchar(100),    
id int
)
INSERT INTO @GPM (gname,    mem,    id)
VALUES('admin','admin',1),
('admin','dom',2),
('oper','''',3),
('Guest','Guest',4),
('lg','oper',6),
('usr','db1',7),
('usr','db2',8),
('nw opr','',''),
('tcnt','',''),
('usr','dc',9),
('dom','at',10),
('dc','sy','')


;With CTE
AS
(

SELECT g.gname,g.mem,CAST(gname + isnull('->' + nullif(mem,gname),'') AS varchar(max)) AS [path]
from @GPM g
INNER JOIN  @GUsr u
ON g.id = u.id

UNION ALL

SELECT g.gname,g.mem,CAST(c.[path] + '->' + g.mem AS varchar(max))
FROM CTE c
INNER JOIN @GPM g
ON g.gname = c.mem
WHERE g.gname <> g.mem
)

SELECT *
FROM CTE


output
------------------------------------------------
gname	mem	path
------------------------------------------------
admin	admin	admin
Guest	Guest	Guest
usr	dc	usr->dc
dc	sy	usr->dc->sy
admin	dom	admin->dom
dom	at	admin->dom->at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 15:26:29

Thanks you Sir :)

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:30:19

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 16:08:11

I just confirmed with my lead that this query is fetching data better than earlier one..
I got more than what I need, from this site..

I keep asking stupid questions,:) I hope soon I'll start answering them as well on this site...

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 16:10:56

quote:
Originally posted by aakcse

I just confirmed with my lead that this query is fetching data better than earlier one..
I got more than what I need, from this site..

I keep asking stupid questions,:) I hope soon I'll start answering them as well on this site...

-Neil

Once you follow this site closely and apply yourselves understanding the concepts/topics its not a long way to go

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 16:47:57

Thanks again, I am big fan of 2 forum: One is Oracle & SqlTeam, MicroSoft should support this site and bring in more people... and number of users.. Though I am quite satisfied with the way I am getting my issues resolved on SQL Server.

I wish Sybase should could also had one site like this the response on Sybase forums is very very slow, & I think DB2 MySql might already have though I don't know, would be very happy if someone share this information as well :)

I found SqlTeam in 2005, when I was not knowing anything about SQLServer. I found SQL Team and Oracle forum very useful as I am working on migration of business logic in different databases.

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 17:04:27

Hi Visakh,

To the above query I added one more table to get more columns, I added to both the select when I run this I am getting below error for all the new col added

Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "u.colName" could not be bound.

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 17:21:07

quote:
Originally posted by aakcse

Hi Visakh,

To the above query I added one more table to get more columns, I added to both the select when I run this I am getting below error for all the new col added

Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "u.colName" could not be bound.

-Neil

do you've the column in table referred by alias u? if not please give correct alias to point to actual table which contains the column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 18:07:20

Hi Visakh,

below is my query, I have cross checked the table name and columns they are right


Query delete can refer to previous post for understanding this

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 18:56:23

you dont have table with alias u in recursive part. so it should be u2 i guess


with cte as
(
SELECT g.GroupName,g.member,u.lastlogin ,CAST(g.groupname + isnull('->' + nullif(g.member,g.groupname),'') AS varchar(max)) AS [path]
,u.[Description],u2.FullName,u.BadPasswordAttempts,u.PasswordAge,u2.PasswordRequired,u2.PasswordExpires
from groupmembers g INNER JOIN  users u 
ON g.ObjectSID = u.ObjectSID
INNER JOIN users2 u2 ON u2.sid=g.ObjectSid
UNION ALL
SELECT g.GroupName,g.member,u2.lastlogin ,CAST(c.[path] + '->' + g.member AS varchar(max))
,u2.[Description],u2.FullName,u2.BadPasswordAttempts,u2.PasswordAge,u2.PasswordRequired,u2.PasswordExpires
FROM CTE c INNER JOIN groupmembers g
ON g.GroupName = c.Member
INNER JOIN users2 u2 ON u2.sid=g.ObjectSid
WHERE g.GroupName <> g.Member
)
SELECT * FROM CTE

i didnt understand reason for inclusion of this new table (user2) though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-08 : 19:23:08

Thanks Visakh,
not all data is present in U alone there is another table u2 which gives the other columns hence I included, will the above query works now? my server went down just now... waiting for it to be up.

Also one request once I am able to run the code, I would request you sir to remove only this query as it is from server code.. I'll also delete it from my post.

As this is already resolved and we have simple best e.g. resolved by you.. this query might confuse other :)

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-09 : 03:02:34

Thanks this worked for me..

-Neil

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources