Mysql – How to write a multi row AND query

MySQLquery

I have a table that keeps tags and their reference. For example:

| tag  | tableName | rowId |
| ---- | --------- | ----- |
| tag1 | table1    | 1     |
| tag2 | table1    | 1     |
| tag1 | table2    | 1     |

I would like to form a query that says something like "get distinct table name and rowId WHERE it has all of these tags: 'tag1', 'tag2'"

The objective of this table is to make it easy to look up rows in tables by tags. Maybe I'm going the complete wrong direction with how to solve this.

How can I do this and keep performance high?

Thanks for the help.

Best Answer

OK, I believe that I have a satisfactory answer.

SELECT tableName, rowId
  FROM tags 
  WHERE tag IN ('tag1', 'tag2') 
  GROUP BY tableName, rowId 
  HAVING COUNT(tag) = 2;

I'm pretty new to this, but this query allows it to find all matches and group them by tableName and rowId. Before returning the results it limits the rows to those that matched exactly the number of tags being searched on.

If anyone knows a better way to do this I'm up for suggestions. Thanks.

Edit 07/06/2018

@akina and @RickJames both pointed out that this query only works if the rows are unique and offered two solutions to address this. See the comments below.

Related Question