CODEWITHSUNDEEP
sqlrelational-database
Tom Gullen
Tom Gullen

Reputation: 61737

SQL Help - Filter by User ID

SELECT     projectID, urlID, COUNT(1) AS totalClicks, projectPage,
                          (SELECT     COUNT(1)
                            FROM          tblStatSessionRoutes, tblStatSessions
                            WHERE      tblStatSessionRoutes.statSessionID = tblStatSessions.ID AND tblStatSessions.projectID = tblAdClicks.projectID AND 
                                                   (tblStatSessionRoutes.leftPageID = tblAdClicks.projectPage OR
                                                   tblStatSessionRoutes.rightPageID = tblAdClicks.projectPage)) AS totalViews
FROM         tblAdClicks
GROUP BY projectID, urlID, projectPage
ORDER BY projectID, urlID

tblProjects has a field UserID, and I need this query to only show records where the project has userID = 5, so as to not show all the stats for all projects from all users

Upvotes: 0

Views: 832

Answers (2)

user554546
user554546

Reputation:

Well, I don't know the structure of the data that you have, but if nothing else, you can always join to the following subquery:

select projectID from tblProjects where userID=5

As to what column(s) you should use for the join, I can't really tell you, since I don't know the specifics of your data.

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

Naively:

SELECT     projectID, urlID, COUNT(1) AS totalClicks, projectPage,
                          (SELECT     COUNT(1)
                            FROM          tblStatSessionRoutes, tblStatSessions
                            WHERE      tblStatSessionRoutes.statSessionID = tblStatSessions.ID AND tblStatSessions.projectID = tblAdClicks.projectID AND 
                                                   (tblStatSessionRoutes.leftPageID = tblAdClicks.projectPage OR
                                                   tblStatSessionRoutes.rightPageID = tblAdClicks.projectPage)) AS totalViews
FROM         tblAdClicks
WHERE projectID IN (SELECT projectID FROM tblProjects WHERE userID = 5)
GROUP BY projectID, urlID, projectPage
ORDER BY projectID, urlID

But it might be better with a join. As always, check your execution plan

Upvotes: 2

Related Questions