February 27, 2010 at 4:28 am
I want a sql query
Following is Tags of User1.
USER1 :- A,B,C,D,E,F
And other users like User2, User3, User4, User5 has associated User1 Tags.
USER2 :- A,B,C,D
USER3 :- C,D,E,F
USER4 :- A,B
USER5 :- C,E,F
Count of A Tag group by User :- 2
Count of B Tag group by User :- 2
Count of C Tag group by User :- 3
Count of D Tag group by User :- 2
Count of E Tag group by User :- 2
Count of F Tag group by User :- 2
Total - 13
USER2 - A,B,C,D :- 2 + 2 + 3 + 2 = 9
USER3 - C,D,E,F :- 3 + 2 + 2 + 2 = 9
USER4 - A,B :- 2 + 2 = 4
USER5 - C,E,F :- 3 + 2 + 2 = 7
What i want to show the result fetch tags of User1 and other User which are associated with his tags priority wise
of users tag sum
Final Result is like as following:
USER2 - A,B,C,D :- 2 + 2 + 3 + 2 = 9
USER3 - C,D,E,F :- 3 + 2 + 2 + 2 = 9
USER5 - C,E,F :- 3 + 2 + 2 = 7
USER4 - A,B :- 2 + 2 = 4
My tables are
Tags :- Id, TagName
UserTags :- Id, TagId, UserId, TagType
Users :- Id, FirstName, LastName
February 27, 2010 at 5:10 am
Your verbal description doesn't match your table structure:
USER2 :- A,B,C,D
vs. UserTags :- Id, TagId, UserId, TagType
Do you have the data available in a relational structure or are those values concatenated in one row?
Please read and follow the first link in my signature onhow to post sample data so we have something to work with. A verbal description of sample data will never be as precise as SQL statements... 😉
February 27, 2010 at 5:22 am
CREATE TABLE TempTags (Id int identity(1,1), TagName Varchar(50))
create table TempUserTags (Id int identity(1,1), TagId int, UserId int, TagType int)
create table TempUsers (Id int identity(1,1), FirstName varchar(50), LastName varchar(50))
insert into dbo.TempUsers(FirstName, LastName)
select 'Ravi','Sharma'
union
select 'Pawan','Bali'
union
select 'Dalvir','Singh'
union
select 'Amarjot','Teja'
union
select 'Jaspreet','Singh'
union
select 'Sandeep','Kumar'
insert into dbo.TempTags(TagName)
select 'Sharepoint'
union
select 'XML'
union
select 'SQL Server'
union
select 'C++'
union
select 'Java'
union
select 'C Sharp'
union
select 'VB.Net'
union
select 'HTML'
insert into dbo.TempUserTags(UserId,TagId,TagType)
select 1,1,1
union
select 1,2,1
union
select 1,3,1
union
select 1,4,2
union
select 1,5,2
union
select 1,6,2
insert into dbo.TempUserTags(UserId,TagId,TagType)
select 2,7,1
union
select 2,8,2
union
select 2,1,1
union
select 2,2,1
union
select 2,3,1
union
select 2,4,2
union
select 3,3,1
union
select 3,4,2
union
select 3,5,2
union
select 3,6,2
union
select 4,1,1
union
select 4,2,1
union
select 5,3,1
union
select 5,5,1
union
select 6,3,1
union
select 6,6,2
union
select 6,5,2
select * from dbo.TempUsers
select * from dbo.TempTags
select * from dbo.TempUserTags
February 27, 2010 at 5:42 am
C Sharp existence in all users = 2
C++ existence in all users = 2
HTML existence in all users = 4
Java existence in all users = 2
SQL Server existence in all users = 3
VB.Net existence in all users = 2
How to calculate following:-
Jaspreet, Singh| HTML, Java, SQL Server, VB.Net | 4 + 2 + 3 + 2 = 11
Dalvir, Singh| C Sharp, C++, HTML, Java | 2 + 2 + 4 + 2 = 10
Sandeep, Kumar| HTML, SQL Server, VB.Net | 4 + 3 + 2 = 9
Ravi, Sharma| HTML, SQL Server | 4 + 3 = 7
Pawan, Bali| C Sharp, C++ | 2 + 2 = 4
Final Resultset
Jaspreet, Singh| HTML, Java, SQL Server, VB.Net
Dalvir, Singh| C Sharp, C++, HTML, Java
Sandeep, Kumar| HTML, SQL Server, VB.Net
Ravi, Sharma| HTML, SQL Server
Pawan, Bali| C Sharp, C++
February 27, 2010 at 5:46 am
order by totaloftags desc
February 27, 2010 at 6:31 am
There seems to be something wrong in your sample data:
table dbo.TempUserTags has three entries for TagId=1 (C Sharp). (ID 1,7, and 17)
Why do you expect C Sharp existence in all users = 2?
Same for most of your other expected results...
Please clarify.
February 27, 2010 at 7:36 am
This relationship refers to one to many. Tag created by one user also used by other multiple users.
Actually all this is like twitter tags functionality.
February 27, 2010 at 8:17 am
jaspreetsingh8 (2/27/2010)
This relationship refers to one to many. Tag created by one user also used by other multiple users.Actually all this is like twitter tags functionality.
That doesn't answer my question.
I asked specific questions related to your sample data and it would be great if you could answer those questions.
February 27, 2010 at 8:47 am
When user create any tag then it cannot be inserted in TempTag table if it already exists. But 1 tag which created by a user also associated with other users.
For example
I create a tag named SQL Server.
Other users also used that tag in their profiles.
Each user create his own tags but he also interrested in other user tags. Then he relates him to other user tags also.
If that doesn't exact ans which u want then plz u explain me abt ur question.
February 27, 2010 at 9:39 am
lmu92 (2/27/2010)
There seems to be something wrong in your sample data:table dbo.TempUserTags has three entries for TagId=1 (C Sharp). (ID 1,7, and 17)
Why do you expect C Sharp existence in all users = 2?
Same for most of your other expected results...
Please clarify.
Please look at my post from above and compare your sample data with your expected result set.
Let me try again:
Your sample data include the following statement (other insert statements removed for readability)
insert into dbo.TempUserTags(UserId,TagId,TagType)
select 1,1,1
union
select 2,1,1
union
select 4,1,1
When counting thoses insert statements it becomes obvious that you're adding three (3) rows with TagId = 1.
The result of your insert statement for dbo.TempTags is (at least for my system)
IdTagName
1C Sharp
2C++
3HTML
4Java
5Sharepoint
6SQL Server
7VB.Net
8XML
So, TagId =1 will match "C Sharp" and has three entries. That doesn't match your statement
C Sharp existence in all users = 2
Did you actually try your SQL code before posting whether it gives you the expected results or not?
I think the issue is within your INSERT statements:
Since you used UNION instead of UNION ALL in your insert statements you forced an internal GROUP BY TagName. This will result in a different order than you wrote your INSERT statement.
So, please clarify.
March 2, 2010 at 12:20 am
declare @Tags table( Id int identity(1,1),TagName varchar(100))
declare @UserTags table (Id int identity(1,1), UserId int,TagId int)
declare @Users table ( Id int identity(1,1), FirstName varchar(100))
insert into @Users select 'Ravi Sharma' union ALL select 'Pawan Bali' union ALL select 'Dalvir Singh' union ALL select 'Amarjot Teja' union ALL select 'Jaspreet Singh' union ALL select 'Sandeep Kumar'
insert into @Tags select 'Sharepoint' union all select 'XML' union all select 'SQL Server' union all select 'C++' union all select 'Java' union all select 'C Sharp' union ALL select 'VB.Net' union ALL select 'HTML'
insert into @UserTags select 1,1 union all select 1,2 union all select 1,3 union all select 1,4 union all select 1,5 union all select 1,6 union all select 2,1 union all select 2,2 union all select 2,3 union all select 2,4 union all select 3,3 union all select 3,4 union all select 3,5 union all select 3,6 union all select 4,1 union all select 4,2 union all select 5,3 union all select 5,5 union all select 5,6
--select * from @Tags
--select * from @Users
--select * from @UserTags
select TU.*, TT.*, TUT.* from @Users TU INNER JOIN @UserTags TUT ON TU.Id = TUT.UserId INNER JOIN @Tags TT ON TT.Id = TUT.TagId
ResultSet
----------------------------------------------------------------------
Id FirstName TagId TagName
----------------------------------------------------------------------
1 Ravi Sharma 1 Sharepoint
2 Pawan Bali 1 Sharepoint
4 Amarjot Teja 1 Sharepoint
1 Ravi Sharma 2 XML
2 Pawan Bali 2 XML
4 Amarjot Teja 2 XML
1 Ravi Sharma 3 SQL Server
2 Pawan Bali 3 SQL Server
3 Dalvir Singh 3 SQL Server
5 Jaspreet Singh 3 SQL Server
1 Ravi Sharma 4 C++
2 Pawan Bali 4 C++
3 Dalvir Singh 4 C++
1 Ravi Sharma 5 Java
3 Dalvir Singh 5 Java
5 Jaspreet Singh 5 Java
1 Ravi Sharma 6 C Sharp
3 Dalvir Singh 6 C Sharp
5 Jaspreet Singh 6 C Sharp
In the above resultset
Count of Sharepoint = 3
Count of XML = 3
Count of SQL Server = 4
Count of C++ = 3
Count of Java = 3
Count of C Sharp = 3
My Query
select distinct TU.FirstName AS ScreenName, ( LTRIM(STUFF((SELECT ', ' + tT.TagName FROM @Tags tT INNER JOIN @UserTags tTUT ON tT.Id = tTUT.TagId WHERE tTUT.UserId = TU.Id FOR XML PATH('')),1,1,'')) )As TagName, SUM(TUT.TagId) as TagSum from @Users TU INNER JOIN @UserTags TUT ON TU.Id = TUT.UserId INNER JOIN @Tags TT ON TT.Id = TUT.TagId group by TU.FirstName, TU.Id order by TagSum desc
---------------------------------------------------------------------------------------
ScreenName | TagName | TagSum
---------------------------------------------------------------------------------------------
Ravi Sharma | Sharepoint, XML, SQL Server, C++, Java, C Sharp | 21
Dalvir Singh | SQL Server, C++, Java, C Sharp | 18
Jaspreet Singh | SQL Server, Java, C Sharp | 14
Pawan Bali | Sharepoint, XML, SQL Server, C++ | 10
Amarjot Teja | Sharepoint, XML | 3
Above output is not correct. What i want in final resultset is like following
------------------------------------------------------------------------------------
ScreenName | TagName | TagSum
-----------------------------------------------------------------------------------------
Ravi Sharma | Sharepoint, XML, SQL Server, C++, Java, C Sharp | 19 (3+3+4+3+3+3)
Pawan Bali | Sharepoint, XML, SQL Server, C++ | 13 (3+3+4+3)
Dalvir Singh | SQL Server, C++, Java, C Sharp | 13 (4+3+3+3)
Jaspreet Singh | SQL Server, Java, C Sharp | 10 (4+3+3)
Amarjot Teja | Sharepoint, XML | 6 (3+3)
March 2, 2010 at 12:25 am
declare @Tags table( Id int identity(1,1),TagName varchar(100))
declare @UserTags table (Id int identity(1,1), UserId int,TagId int)
declare @Users table ( Id int identity(1,1), FirstName varchar(100))
insert into @Users
select 'Ravi Sharma' union ALL select 'Pawan Bali' union ALL select 'Dalvir Singh' union ALL
select 'Amarjot Teja' union ALL select 'Jaspreet Singh' union ALL select 'Sandeep Kumar'
insert into @Tags
select 'Sharepoint' union all select 'XML' union all select 'SQL Server' union all select 'C++' union all
select 'Java' union all select 'C Sharp' union ALL select 'VB.Net' union ALL select 'HTML'
insert into @UserTags
select 1,1 union all select 1,2 union all select 1,3 union all
select 1,4 union all select 1,5 union all select 1,6 union all
select 2,1 union all select 2,2 union all select 2,3 union all select 2,4 union all
select 3,3 union all select 3,4 union all select 3,5 union all select 3,6 union all
select 4,1 union all select 4,2 union all
select 5,3 union all select 5,5 union all select 5,6
--select * from @Tags
--select * from @Users
--select * from @UserTags
select
TU.*,
TT.*,
TUT.*
from
@Users TU INNER JOIN @UserTags TUT
ON TU.Id = TUT.UserId
INNER JOIN @Tags TT
ON TT.Id = TUT.TagId
ResultSet
----------------------------------------------------------------------
IdFirstNameTagIdTagName IdUserIdTagId
----------------------------------------------------------------------
1Ravi Sharma1Sharepoint111
2Pawan Bali1Sharepoint721
4Amarjot Teja1Sharepoint1541
1Ravi Sharma2XML 212
2Pawan Bali2XML 822
4Amarjot Teja2XML 1642
1Ravi Sharma3SQL Server313
2Pawan Bali3SQL Server923
3Dalvir Singh3SQL Server1133
5Jaspreet Singh3SQL Server1753
1Ravi Sharma4C++ 414
2Pawan Bali4C++ 1024
3Dalvir Singh4C++ 1234
1Ravi Sharma5Java 515
3Dalvir Singh5Java 1335
5Jaspreet Singh5Java 1855
1Ravi Sharma6C Sharp 616
3Dalvir Singh6C Sharp 1436
5Jaspreet Singh6C Sharp 1956
In the above resultset
Count of Sharepoint = 3
Count of XML = 3
Count of SQL Server = 4
Count of C++ = 3
Count of Java = 3
Count of C Sharp = 3
My Query
select distinct
TU.FirstName AS ScreenName,
(
LTRIM(STUFF((SELECT
', ' + tT.TagName
FROM @Tags tT
INNER JOIN @UserTags tTUT ON tT.Id = tTUT.TagId
WHERE tTUT.UserId = TU.Id
FOR XML PATH('')),1,1,''))
)As TagName,
SUM(TUT.TagId) as TagSum
from
@Users TU INNER JOIN @UserTags TUT
ON TU.Id = TUT.UserId
INNER JOIN @Tags TT
ON TT.Id = TUT.TagId
group by TU.FirstName, TU.Id
order by TagSum desc
-------------------------------------------------------------------------------
ScreenName| TagName | TagSum
------------------------------------------------------------------------------
Ravi Sharma| Sharepoint, XML, SQL Server, C++, Java, C Sharp | 21
Dalvir Singh| SQL Server, C++, Java, C Sharp | 18
Jaspreet Singh| SQL Server, Java, C Sharp | 14
Pawan Bali| Sharepoint, XML, SQL Server, C++ | 10
Amarjot Teja| Sharepoint, XML | 3
What i want in final resultset is like following according to count of tags in each user.
-------------------------------------------------------------------------------
ScreenName| TagName | TagSum
------------------------------------------------------------------------------
Ravi Sharma| Sharepoint, XML, SQL Server, C++, Java, C Sharp | 19 (3+3+4+3+3+3)
Pawan Bali| Sharepoint, XML, SQL Server, C++ | 13 (3+3+4+3)
Dalvir Singh| SQL Server, C++, Java, C Sharp | 13 (4+3+3+3)
Jaspreet Singh| SQL Server, Java, C Sharp | 10 (4+3+3)
Amarjot Teja| Sharepoint, XML | 6 (3+3)
March 2, 2010 at 10:14 am
HERE IS THE CODE, HAVE FUN :-):
DECLARE @TEMPTABLE TABLE(USERID INT, USERNAME VARCHAR(MAX), TAGNAME VARCHAR(MAX), USERCOUNT INT)
INSERT INTO @TEMPTABLE
SELECT USERS.Id AS USERID, USERS.FirstName +', '+USERS.LastName AS USERNAME,A.TagName , A.USERSCOUNT FROM (SELECT TECHDESC.Id ,TECHDESC.TagName ,COUNT(TECH.USERID) AS USERSCOUNT FROM @TempUserTags TECH
INNER JOIN @TempTags TECHDESC ON TECH.TagId = TECHDESC.Id
GROUP BY TagId,TECHDESC.Id, TECHDESC.TagName) A
INNER JOIN @TempUserTags B ON A.Id = B.TAGID
INNER JOIN @TempUsers USERS ON B.UserId = USERS.Id
GROUP BY USERS.Id , USERS.FirstName +', '+USERS.LastName, A.TagName , A.USERSCOUNT
--SELECT * FROM @TEMPTABLE (JUSTCHEECKING)
SELECT TEMP.USERNAME, SUBSTRING((select ',', i.TAGNAME as "text()" from @TEMPTABLE i WHERE I.USERID = USERS.ID FOR xml path('')), 2 , 8000) AS NAME , SUM(USERCOUNT) AS COUNT FROM @TempUsers USERS
INNER JOIN @TEMPTABLE TEMP ON USERS.Id = TEMP.USERID
GROUP BY USERS.ID, TEMP.USERNAME
March 2, 2010 at 10:17 am
I THINK YOU CAN DO SOME FORMATTING.......ELSE JUST LET ME KNOW....nimbala29@gmail.com
March 2, 2010 at 10:33 am
sorry i've used table datatypes for your tables toavoid creating tables in my database
here is the code again
DECLARE @TEMPTABLE TABLE(USERID INT, USERNAME VARCHAR(MAX), TAGNAME VARCHAR(MAX), USERCOUNT INT)
INSERT INTO @TEMPTABLE
SELECT USERS.Id AS USERID, USERS.FirstName +', '+USERS.LastName AS USERNAME,A.TagName , A.USERSCOUNT FROM (SELECT TECHDESC.Id ,TECHDESC.TagName ,COUNT(TECH.USERID) AS USERSCOUNT FROM dbo.TempUserTags TECH
INNER JOIN dbo.TempTags TECHDESC ON TECH.TagId = TECHDESC.Id
GROUP BY TagId,TECHDESC.Id, TECHDESC.TagName) A
INNER JOIN dbo.TempUserTags B ON A.Id = B.TAGID
INNER JOIN dbo.TempUsers USERS ON B.UserId = USERS.Id
GROUP BY USERS.Id , USERS.FirstName +', '+USERS.LastName, A.TagName , A.USERSCOUNT
--SELECT * FROM @TEMPTABLE (JUSTCHEECKING)
SELECT TEMP.USERNAME, SUBSTRING((select ',', i.TAGNAME as "text()" from @TEMPTABLE i WHERE I.USERID = USERS.ID FOR xml path('')), 2 , 8000) AS NAME , SUM(USERCOUNT) AS COUNT FROM dbo.TempUsers USERS
INNER JOIN @TEMPTABLE TEMP ON USERS.Id = TEMP.USERID
GROUP BY USERS.ID, TEMP.USERNAME
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply