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.


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.

  • 1
    There is no unique constraints, so duplicates are possible. You must use HAVING COUNT(DISTINCT tag) = 2.
    – Akina
    Jul 6 '18 at 4:25
  • 1
    If you want performance, do not allow duplicate rows. Do that with PRIMARY KEY(tableName, rowId, tag) and INSERT IGNORE. Note: This PK is also needed to make this code run fast. (And, of course, use InnoDB.)
    – Rick James
    Jul 6 '18 at 4:29
  • @RickJames I know it. But I see the question text posted by the author - there is no any allusion about unique indices/constraints in it.
    – Akina
    Jul 6 '18 at 4:32
  • Thank you both for your comments. I had not thought about the duplicates issue and I've added the primary key as @RickJames suggested.
    – James
    Jul 6 '18 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.