MySQL: Querying on multiple rows

Portal Home > Knowledgebase > Articles Database > MySQL: Querying on multiple rows

Posted by qbert220, 08-07-2008, 11:43 AM
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: 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?
Posted by orbitz, 08-07-2008, 12:09 PM
SELECT id FROM table WHERE val='a' or val='b'; or SELECT id FROM table WHERE val IN ('a', 'b');
Posted by Czaries, 08-07-2008, 12:53 PM
Always use the second example here with the 'IN' clause - It will execute MUCH faster for tables with lots and lots of rows.
Posted by qbert220, 08-07-2008, 01:55 PM
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'.
Posted by foobic, 08-07-2008, 07:14 PM
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:
Posted by qbert220, 08-08-2008, 10:02 AM
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): Any problems with this? Is there a better way?
Posted by Doh004, 08-08-2008, 10:08 AM
As long as you set up your indexes correctly, you shouldn't have too big of a problem.
Posted by Czaries, 08-08-2008, 12:36 PM
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.
Posted by foobic, 08-08-2008, 11:16 PM
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?

Add to Favourites Print this Article