I need some help doing a MySQL query. here is a simplfied example showing what I am trying to do:

I have a table containing the following:


id val
1 a
1 b
2 a
2 c

I want to be able to query the table and get results showing ids based on the val column.

For a single val value I use:

SELECT id FROM table WHERE val='a'

This returns id values 1 and 2.

I want to be able to query on multiple val values. For example I want to query ids that have a row in the table with the value 'a' and a row with the value 'b'. This query would return id 1. Any ideas how I can do this?

SELECT id FROM table WHERE val='a' or val='b';

or

SELECT id FROM table WHERE val IN ('a', 'b');

SELECT id FROM table WHERE val='a' or val='b';

or

SELECT id FROM table WHERE val IN ('a', 'b');

Always use the second example here with the 'IN' clause - It will execute MUCH faster for tables with lots and lots of rows.

SELECT id FROM table WHERE val='a' or val='b';

or

SELECT id FROM table WHERE val IN ('a', 'b');

These return rows 1, 2 and 3 since these contain 'a' or 'b'. I only want rows 1 and 2 back (rows for which id matches 'a' and 'b') because there is no match where id is 2 and val='b'.

For that I think you need to join the table to itself. It's probably not an ideal design and won't scale well, but something like this should work:
SELECT first.id FROM table AS first
JOIN table AS second USING (id)
WHERE first.val='a' AND second.val='b';

I'm needing something that work work efficiently with large tables (many thousands of entries, perhaps a million) and several values of 'val'.

I've come up with the following which does what I want (on a small test case anyway):

SELECT id, COUNT(id) AS count FROM table WHERE val IN('a', 'b') GROUP BY id HAVING count=2

Any problems with this? Is there a better way?

As long as you set up your indexes correctly, you shouldn't have too big of a problem.

The general rule of thumb to use is that when you use a conditional clause on a column (i.e. WHERE), that column should be an index. Of course, there are many times where this is not necessarily true (because you don't want to index every single column), but most of the times it should be. Conditions on columns that are not indexes result in a full table scan, which gets gets slower the more rows your table has. For most uses and smaller sites it's OK, but for tables with >~ 10,000 rows and/or queries that are performed very frequently, you will notice a difference.

SELECT id, COUNT(id) AS count FROM table WHERE val IN('a', 'b') GROUP BY id HAVING count=2

Any problems with this? Is there a better way?
That's definitely a better option than my self-join suggestion, and may be all you need (with an index on val, for sure). But perhaps it's possible to change your schema and use a simpler query?