July 12, 2012 at 5:29 am
I have 2 tables in the database, UserMast and ProfileMast.
UserMast
(
UserID uniqueidentifier, (pk)
EmailID varchar(100),
ParentID uniqueidentifier, (fk)
)
ProfileMast
(
ProfileID uniqueidentifier, (pk)
UserID uniqueidentifier, (fk)
ProfileName varchar(100)
)
Now the tqo tables are connected the UserID column.
UserMast is having the ParentID column, this contains the userID of this table again and this hirearchy is multilevel.
ProfileMast table can have multiple records for each UserID
What I need is Count of profiles each user has assiciated with him or beneath his associated users
e.g.
A is parent of B,E
B is parent of C
C is parent of D
A has 2 profiles, B has 1 profile, C has 5 profiles and D has 3 profile, E has 2 profiles.
Now the query should return data like below
UserParentUserProfileCountHint for calc
Anull13(2+1+5+3+2)
BA9(1+5+3)
CB8(5+3)
DC33
EA22
Please help me in writing sql query for this.
Thanks
July 12, 2012 at 5:35 am
Hello and welcome to ssc. Please have a read of the best practices / forum etiquette article here[/url].
This looks straightforward - if you can post some easily-consumable data as suggested in the doc, you will have a few folks working on a solution in no time.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2012 at 10:52 pm
I agree with Chris - a very straightforward solution can be made available if:
1. You convert your table definitions to actual, runnable DDL.
2. Give us some sample inserts for both the customer master and profile master, that ties the uniqueidentifies together properly.
The latter I think is the reason most people would shy away from trying to help (#1 is easy).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 13, 2012 at 12:15 am
Sorry for the trouble guys I should have posted the scripts earlier.
Here are the scripts for table creation and data population:
Create Table UserMast
(
UserID uniqueidentifier default newid(),
EmailID varchar(100),
ParentID uniqueidentifier,
)
Create Table ProfileMast
(
ProfileID uniqueidentifier default newid(),
UserID uniqueidentifier,
ProfileName varchar(100)
)
insert into UserMast (UserID, EmailID, ParentID) values ('FB259D9E-2386-461D-9DAC-22D3BDA5F6C7','A',null)
insert into UserMast (UserID, EmailID, ParentID) values ('055A2C49-3829-471A-921F-9633DB73F0DD','B','FB259D9E-2386-461D-9DAC-22D3BDA5F6C7')
insert into UserMast (UserID, EmailID, ParentID) values ('1A2FBFDD-4119-483A-B9F4-CD4837C25055','C','055A2C49-3829-471A-921F-9633DB73F0DD')
insert into UserMast (UserID, EmailID, ParentID) values ('FE0CC778-0BDE-4DFE-8A16-898B9C8D8DDE','D','1A2FBFDD-4119-483A-B9F4-CD4837C25055')
insert into UserMast (UserID, EmailID, ParentID) values ('B7429930-1FE2-4CC6-BF04-A4A06DAC1102','E','FB259D9E-2386-461D-9DAC-22D3BDA5F6C7')
select * from usermast
insert into ProfileMast (ProfileID,UserID,ProfileName) values ('B4E45E93-3C35-4367-B2A3-B675AE57D9C7','FB259D9E-2386-461D-9DAC-22D3BDA5F6C7','P1')
insert into ProfileMast (ProfileID,UserID,ProfileName) values ('07C1AF04-A816-4531-A773-1AD3F62D4C84','055A2C49-3829-471A-921F-9633DB73F0DD','P2')
insert into ProfileMast (ProfileID,UserID,ProfileName) values ('1148E2AF-4E05-4F81-87B3-2AACCEA49495','1A2FBFDD-4119-483A-B9F4-CD4837C25055','P3')
insert into ProfileMast (ProfileID,UserID,ProfileName) values ('DB099E90-6B7A-4654-9ACC-3E9122099AEE','FE0CC778-0BDE-4DFE-8A16-898B9C8D8DDE','P4')
insert into ProfileMast (ProfileID,UserID,ProfileName) values ('9346388B-1D72-4E6F-AB7D-B04181E27548','B7429930-1FE2-4CC6-BF04-A4A06DAC1102','P5')
select * from ProfileMast
July 13, 2012 at 12:52 am
This may help you
SELECT
X.*,Y.*
FROM USERMAST X
CROSS APPLY
(SELECT COUNT(*) CNT FROM USERMAST Y WHERE X.USERID=Y.PARENTID) y
July 13, 2012 at 2:48 am
Thanks for the reply SSC Eights!, however I require a different output.
The query you posted returns the number of childs of each user, instead I need the number of profiles a user has along with his sub-users, please see the example in question.
Thanks for your time and efforts.
July 13, 2012 at 3:11 am
I believe you're looking for something that handles as many levels as necessary, like this:
Create Table #UserMast
(
UserID uniqueidentifier default newid(),
EmailID varchar(100),
ParentID uniqueidentifier,
)
Create Table #ProfileMast
(
ProfileID uniqueidentifier default newid(),
UserID uniqueidentifier,
ProfileName varchar(100)
)
insert into #UserMast (UserID, EmailID, ParentID) values ('FB259D9E-2386-461D-9DAC-22D3BDA5F6C7','A',null)
insert into #UserMast (UserID, EmailID, ParentID) values ('055A2C49-3829-471A-921F-9633DB73F0DD','B','FB259D9E-2386-461D-9DAC-22D3BDA5F6C7')
insert into #UserMast (UserID, EmailID, ParentID) values ('1A2FBFDD-4119-483A-B9F4-CD4837C25055','C','055A2C49-3829-471A-921F-9633DB73F0DD')
insert into #UserMast (UserID, EmailID, ParentID) values ('FE0CC778-0BDE-4DFE-8A16-898B9C8D8DDE','D','1A2FBFDD-4119-483A-B9F4-CD4837C25055')
insert into #UserMast (UserID, EmailID, ParentID) values ('B7429930-1FE2-4CC6-BF04-A4A06DAC1102','E','FB259D9E-2386-461D-9DAC-22D3BDA5F6C7')
--select * from #UserMast
insert into #ProfileMast (ProfileID,UserID,ProfileName) values ('B4E45E93-3C35-4367-B2A3-B675AE57D9C7','FB259D9E-2386-461D-9DAC-22D3BDA5F6C7','P1')
insert into #ProfileMast (ProfileID,UserID,ProfileName) values ('07C1AF04-A816-4531-A773-1AD3F62D4C84','055A2C49-3829-471A-921F-9633DB73F0DD','P2')
insert into #ProfileMast (ProfileID,UserID,ProfileName) values ('1148E2AF-4E05-4F81-87B3-2AACCEA49495','1A2FBFDD-4119-483A-B9F4-CD4837C25055','P3')
insert into #ProfileMast (ProfileID,UserID,ProfileName) values ('DB099E90-6B7A-4654-9ACC-3E9122099AEE','FE0CC778-0BDE-4DFE-8A16-898B9C8D8DDE','P4')
insert into #ProfileMast (ProfileID,UserID,ProfileName) values ('9346388B-1D72-4E6F-AB7D-B04181E27548','B7429930-1FE2-4CC6-BF04-A4A06DAC1102','P5')
--select * from #ProfileMast
;WITH CountProfiles AS (
SELECT UserID, CP=COUNT(ProfileName)
FROM #ProfileMast
GROUP BY UserID
),
ResolveProfileCounts AS (
SELECT a.UserID, EmailID, ParentID, CP, [check]=CAST(CP AS VARCHAR(100))
FROM #UserMast a
INNER JOIN CountProfiles b
ON b.UserID = a.UserID
WHERE ParentID IS NOT NULL
UNION ALL
SELECT b.UserID, b.EmailID, b.ParentID, a.CP + c.CP
,CAST([check] + '+' + CAST(c.CP AS VARCHAR) AS VARCHAR(100))
FROM ResolveProfileCounts a
INNER JOIN #UserMast b ON a.ParentID = b.UserID
INNER JOIN CountProfiles c
ON c.UserID = a.UserID
)
SELECT EmailID, ProfileCount=SUM(CP), [check]=MAX([check])
FROM ResolveProfileCounts
GROUP BY EmailID
DROP TABLE #ProfileMast, #UserMast
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 13, 2012 at 6:05 am
Hi dwain.c,
Thanks for the answer, your solution seems more accurate to me.
I will revert after checking the output.
Thanks a lot 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply