June 4, 2008 at 10:23 am
I have 4 tables
Users{userid(pk),name,no}
GroupUsers{userid(pk),usergroupid(pk)}
UserGroups{UserGroupid(pk),Name}
UserGroupUserGroups{Parentusergroupid(pk),childusergroupid}
relationships:
FK_UserGroupUsers_Users
FK_UserGroupUsers_UserGroups
FK_UserGroupUserGroups_UserGroups1
FK_UserGroupUserGroups_UserGroups
I would like to have a view ot CTE's what ever but the result i need some thins like
Users Groups
X A
X B
Y A
Z C
X D
From the result set
X is a member of group A
Group A is a member of Group B and so X is a member of Group A and Group B
Here Groups has users and also sub groups has users.
June 4, 2008 at 10:30 am
If I were you I'd build a nested set representation of UserGroupUserGroups.
I'd then left join that to GroupUsers and left join your results to users.
June 4, 2008 at 10:31 am
can u give tht in a tsql query since i already gave all my tables and relationships
June 4, 2008 at 10:51 am
Any other idea to deal this?
June 4, 2008 at 11:31 am
It's going to be a stored procedure, another table and a view.
I suggest reading the recent posting on this very site regarding nested set representations of adjacency lists.
Once you get your nested set representation going and give me the schema, theoretically I could give you the SQL (although I've already told you what to do above)
June 4, 2008 at 11:50 am
how to get nested set representation for those 2 talbes?
June 4, 2008 at 11:52 am
search on this site for celko , nested set, adjacency
there was an article recently.
I'll write the code for you, but that will cost you alot of money 🙂
June 4, 2008 at 12:47 pm
I created a view with cte
WITH UserGroupUserGroups AS (
SELECT
CAST(NULL AS UNIQUEIDENTIFIER) AS ParentUserGroupID,
ug.UserGroupID AS ChildUserGroupID
FROM
UserSecurity.UserGroups AS ug
WHERE
NOT EXISTS(SELECT 'X' FROM UserSecurity.UserGroupUserGroups AS ugug WHERE ugug.ChildUserGroupID = ug.UserGroupID)
UNION ALL SELECT
parentGroups.ChildUserGroupID AS ParentUserGroupID,
childGroups.ChildUserGroupID AS ChildUserGroupID
FROM
UserGroupUserGroups AS parentGroups
INNER JOIN UserSecurity.UserGroupUserGroups AS childGroups ON childGroups.ParentUserGroupID = parentGroups.ChildUserGroupID
)
SELECT * FROM UserGroupUserGroups AS ugugf WHERE ParentUserGroupID IS NOT NULL
June 4, 2008 at 1:12 pm
NIce job, I should have mentioned the CTE alternative too. Do you by chance have a link to that article that was up here a couple weeks ago regarding this topic (its a common problem).
June 4, 2008 at 1:48 pm
sorry,i didnt get that article from the search.
since i got the view working, now i have to populate my result set required.
by the way how can i insert some test data into these tables like 1000 records at one shot to check the performance.
June 4, 2008 at 2:18 pm
RedGate has a test data generator. Check their site for it (link at top right of this site).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 2:45 pm
How can i test performance for this query. I dont have any data as of now but want to test performance for future.
June 4, 2008 at 3:03 pm
Additionally, you can insert 1 row and then
insert rowinformation from
select rowinformation i just inserted
This way you will have 2^x number of rows of test data.
I'm sorry if this is too pseudocode, but I'm busy at work today.
June 4, 2008 at 3:19 pm
finally i want to test performance for this
June 4, 2008 at 4:15 pm
When i tried performance check with Execution Plan..
I get cost:94% for scanning the table UserGroupUserGroups when cheking for child and parent group ids.
how do i deal with this cost.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply