MySQL: get multiple-keyed rows matching several WHERE conditions (probably simple)

Question

I have a table of userdata that includes user ID and category IDs. I want to grab all of the users that are associated with multiple category IDs, eg:

id | UserID | CategoryID

1 | 123456 | 999

2 | 123456 | 888

3 | 123457 | 999

4 | 123458 | 777

So for example, if I wanted to get all users with a categoryID of 999, I'd get 123456 and 123457. I need to get all users with a categoryID of 999 and 888, which in this case should only give me user 123456. I can't figure out how to write a query to give me this data.

Can anyone help?

Answer 1

Wouldn't

SELECT DISTINCT tbl1.UserID FROM table_1 AS tbl1 LEFT JOIN table_1 AS tbl2 ON tbl1.UserID = tbl2.UserID WHERE tbl1.CategoryID = 999 AND tbl2.CategoryID = 888

do the jpb? (I'm not sure)

Answer 2

I seem to have solved this myself:

SELECT *, GROUP_CONCAT(CategoryID) AS ids FROM tblWebsiteUserStats
GROUP BY UserID
HAVING ids LIKE '%888%' AND ids LIKE '%999%'

This works!