For those of you looking for information on the TV show “Chuck“, this is not for you. If you don’t understand what that means, then this might be for you. Standard SQL provides a construct called union that combines the contents of two queries, which acts as a logical OR operation against two or more datasets. However, I recently found myself in the need of the similar functionality, but with an AND operation. SQL Server provides a construct called ‘intersect‘ which does just that, but it’s not ANSI SQL, and, since I was using MySQL, that didn’t help me. I did find a way to get the data I needed, however, by using a combination of grouping and the having clause.
Here’s the problem: I had two tables of data that formed what was a many-to-many relationship (a map table was the third table). The first table contained generic data, and the second table contained free-form meta information about the records in the first table. For the purpose of illustration, imagine a set of records with a name, address and astrological sign; each one of those records could have 1 or more free-form meta fields attached to it:
My assignment was to select all records in table a that matched all of the free-form meta fields submitted from a user. At first this seemed simple:
select * from a, b, abmap where a.id = abmap.a_id and abmap.b_id = b.id and b.meta in ('meta1', 'meta2', 'meta3');
Unfortunately, this doesn’t implement an AND; it would include records from table a that had either 1 or 2 of the requested meta values in addition to having all three. I was looking for something like:
select all records that have meta=meta1 AND meta=meta2 AND meta=meta3
What I needed to do was to group on a field I knew had unique values in table a (using the example case, I used ‘name’), then use the having clause to count the number of rows for every name in the result set. If the number of returned rows for any given name matches the number of meta values provided by the user, the record is considered a match.
select a.* from a, b, abmap where a.id = abmap.a_id and abmap.b_id = b.id and b.meta in ('meta1','meta2','meta3') group by a.name having count(a.name) = 3;
The count function normally returns the number of rows in the query, but since the query has been grouped by the name column, count returns the number of rows in each group. Since the user had specified three different meta values, I wanted to find groups with exactly three records. By using this query, you would get all records from table a that had the values ‘meta1′, ‘meta2′ and ‘meta3′ attached to it through abmap, no more, no less.
Tags: mysql