SQL Query Question - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query Question

Author

Topic

jaspreetsingh8
Starting Member

3 Posts

Posted - 2010-02-27 : 06:21:07

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

Jaspreet Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 06:41:20

[code]
SELECT COALESCE(u1.FirstName + ' ','') + COALESCE(u1.LastName,'') AS User,
SUM(t2.TagCount)
FROM Users u1
JOIN UserTags ut1
ON ut1.UserId = u1.rId
JOIN Tags t1
ON t1.Id = ut1.TagId
JOIN(
SELECT t.TagName,
COUNT(*) AS TagCount
FROM Users u
JOIN UserTags ut
ON ut.UserId = u.Id
JOIN Tags t
ON t.Id = ut.TagId
WHERE COALESCE(u.FirstName + ' ','') + COALESCE(u.LastName,'') = 'USER1'
GROUP BY t.TagName
)t2
ON t2.TagName = t1.TagName
WHERE COALESCE(u1.FirstName + ' ','') + COALESCE(u1.LastName,'') <> 'USER1'
GROUP BY COALESCE(u1.FirstName + ' ','') + COALESCE(u1.LastName,'')
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 06:49:53

or using sql 2005 apply


SELECT COALESCE(u.FirstName + ' ','') + COALESCE(u.LastName,''),
SUM(t.TagCnt) 
FROM Users u
CROSS APPLY(SELECT TagId
            FROM UserTags
            WHERE UserId=u.Id
            )ut
OUTER APPLY (SELECT COUNT(*) AS TagCnt
             FROM UserTags ut1
             JOIN Users u1
             ON u1.Id=ut1.UserId
             WHERE COALESCE(u1.FirstName + ' ','') + COALESCE(u1.LastName,'') = 'USER1
             AND ut1.TagId=ut.TagId
             )t
WHERE COALESCE(u.FirstName + ' ','') + COALESCE(u.LastName,'') <> 'USER1'
GROUP BY COALESCE(u.FirstName + ' ','') + COALESCE(u.LastName,'')

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 06:56:15

also see what all you can do with apply operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources