MySQL Forums
Forum List  »  Newbie

Re: Specifying multiple WHERE options within a group?
Posted by: Jay Pipes
Date: June 27, 2005 07:09PM

Callum,

It's not too complicated when you break it down into pieces:

Starting from the top:

SELECT COUNT(DISTINCT ua.UserID)
FROM UserActions ua

gives us a resultset with which to filter our eventual query. The COUNT(DISTINCT ua.UserID) simply counts the number of unique UserID values we get after doing the two INNER JOINs:

The first:

INNER JOIN (
SELECT UserID FROM UserActions
WHERE Action = 'Request Download'
) AS uad
ON ua.UserID = aud.UserID

joins the main result set via an intersection (INNER JOIN) to a copy of the UserActions table filtered for only the 'Download Request' actions. We join on the UserID column.

The second:

INNER JOIN (
SELECT UserID FROM UserActions
WHERE Action = 'Page View'
) AS uap
ON ua.UserID = aup.UserID

does the exact same for the 'Page View' actions.

By doing an INNER JOIN, we know that the only results returned from the join will be those records which intersected. In the case of the first joined table, we're returning the UserIDs having requested a download. The records in the main UserActions resultset (ua) are filtered based on the UserID values returned in the join (uad). The records in the main UserActions resultset (ua) are further filtered for only UserID values returned by the second joined set (uap).

Hope this explains everything!

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.