CODEWITHSUNDEEP
sqlsql-server
user1800361
user1800361

Reputation:

How to count the like

I want to count many likes i have for specific post in my database I had create this simple Query show How many Like I have for one resource"post" but when I add to main script it miss it up 

SELECT     COUNT(Likes.resourceID) AS Count_resID, Resources.Id
FROM         Likes INNER JOIN
                      Resources ON Likes.resourceID = Resources.Id
GROUP BY Resources.Id

Result 
Count_resID Id
----------- -----------
1           53
2           54
2           60
2           61
1           62

(5 row(s) affected)
SELECT     a.Id, a.summary, a.pageId, a.name, a.createdOn, COUNT(Likes.resourceID) AS Count_resID
FROM         Resources AS a INNER JOIN
                      Topics_Resources AS b ON a.Id = b.ResourceID INNER JOIN
                      Skills_Resources AS c ON a.Id = c.ResourceID INNER JOIN
                      Types AS d ON a.typeId = d.Id INNER JOIN
                      Modules AS m ON a.ModuleId = m.ModuleID INNER JOIN
                      ContentItems AS ci ON m.ModuleID = ci.ModuleID INNER JOIN
                      Tabs AS t ON t.TabID = ci.TabID INNER JOIN
                      TabModules AS tb ON t.TabID = tb.TabID INNER JOIN
                      Likes ON a.Id = Likes.resourceID LEFT OUTER JOIN
                      HtmlText AS h ON tb.ModuleID = h.ModuleID
GROUP BY a.Id, a.summary, a.pageId, a.name, a.createdOn 
Id          summary                             pageId                                                            name           createdOn                 Count_resID
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------
53          jihuih                               http://localhost/ideapark/testpage99.aspx                        jklhjk         2013-05-10 07:24:21.833    12
54          1                                    http://localhost/ideapark/testpage33.aspx                        sdvs           2013-05-09 07:24:21.833    2
60          sdvsdv                               http://localhost/ideapark/tesCreatedate.aspx                     dsvsdv         2013-05-13 07:32:13.133    8
61          newtest                              http://localhost/ideapark/newtest.aspx                           newTest       2013-05-13 10:35:08.027     2
62          sdvsdvds                             http://localhost/ideapark/page21.aspx                            svdsvs        2013-05-14 14:06:15.603     35

(5 row(s) affected)

Upvotes: 0

Views: 145

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The problem is that you are joining multiple tables, and more than one of these tables has a 1-n relationship. You are getting Cartesian products where you don't expect it.

The right way to fix this is to do subqueries to summarize before you join.

You do, however, have a quick-and-easy fix using count(distinct). Use this select statement instead:

SELECT     a.Id, a.summary, a.pageId, a.name, a.createdOn,
           COUNT(distinct Likes.Id) AS Count_resID;

This assumes that each record in likes has an id to uniquely identify it.

A better way to fix it (especially if this query will be re-used) involves changing the from joins from:

                  TabModules AS tb ON t.TabID = tb.TabID INNER JOIN
                  Likes ON a.Id = Likes.resourceID LEFT OUTER JOIN

To something like:

                  . . .
                  TabModules AS tb ON t.TabID = tb.TabID INNER JOIN
                  (select l.resourceId, count(*) as NumLikes
                   from Likes l
                  ) l ON a.Id = l.resourceID LEFT OUTER JOIN
                  . . .

And then referencing NumLikes in the select clause.

Upvotes: 1

Ken Gee
Ken Gee

Reputation: 63

Based off of your results change there is an issue with one of the criteria you are using to join your tables. You must make sure Tables in join all have one to one ratio with the base table.

Upvotes: 1

Related Questions