mysql query help

fikse

I am designing a system to track which pages logged in users have browsed within a site.

The first table (users) has the users unique ID, username, password, etc.

The 2nd table (usage) records the page each user loaded... userID, pageloaded, date.

I am trying to select which users have browsed the entire site.... the pages are recorded in the 2nd table as page01, page02, page03, etc...

how can I query the database to give me back a list of users who have browsed all of the pages at least once.....?

this is what I have so far:

=============

SELECT DISTINCT

usage.userID,
users.fname

FROM usage
LEFT JOIN users on usage.userID = users.ID

WHERE usage.page = 'pag01' AND 'page02' AND 'page03'

=============

what I'm putting in the WHERE clause isn't working...

thanks for any help or guidence...

eoghain

You'll need to use the HAVING clause to make a query like this work. I'm not sure if MySQL supports HAVING, but here is what it should look like:

SELECT users.fname 
  FROM users INNER JOIN usage on usage.userID = users.ID 
 WHERE (usage.page = 'page01'
    OR  usage.page = 'page02'
    OR  usage.page = 'page03')
 GROUP BY users.fname
HAVING count(*) = 3;

Now this will only work if you are storing only unique pages, if you are storing every visit to your pages then the user could have visited page01 3 times and this query would return them.

If you have to have users that have visited all 3 pages and you are storing every time a user visits a page then you are going to need a much more complex query using INTERSECT, again I don't know if MySQL supports it. But if it does your query would look something like this:

SELECT users.fname 
  FROM users INNER JOIN usage on usage.userID = users.ID 
 WHERE usage.page = 'page01'

INTERSECT

SELECT users.fname 
  FROM users INNER JOIN usage on usage.userID = users.ID 
 WHERE usage.page = 'page02'

INTERSECT

SELECT users.fname 
  FROM users INNER JOIN usage on usage.userID = users.ID 
 WHERE usage.page = 'page03'

Elena_Mitovska

I think this might work the way you need:

SELECT users.fname, count(distinct usage.page)
FROM users LEFT OUTER JOIN usage on usage.userID = users.ID
GROUP BY users.fname
HAVING count(distinct usage.page) = 3;

fikse

Thanks Elena, I'm pretty sure your query will work!

Brooks