Mysql Join query using a group by

Question

I'm trying to write a query that will return ...

1. Each distinct user id in the accesslog table.

2. A count of how many distinct ip addresses each userid has accessed the site with, also from the accesslog table.

3. That userid's name, email address, and company name, from the users table.

I've got 1 and 2, but I'm not sure how to get 3. I suck at joins, I admit. :(

Here's the query I have now:

SELECT DISTINCT userid, COUNT(ipaddress) AS c FROM accesslog WHERE url LIKE 'www.example.com%' and userid != '' GROUP BY userid ORDER BY c desc

How would I JOIN in the other pieces of data that I want?

Answer 1

You'll need to join to users table and add all its columns to GROUP BY:

SELECT a.userid, u.name, u.email, u.company, COUNT(a.ipaddress) AS c
  FROM accesslog a, USERS u
 WHERE a.userid = u.userid
   AND a.url LIKE 'www.example.com%'
   AND a.userid != ''
GROUP BY a.userid
ORDER BY c desc