Select in a many-to-many relationship in MySQL

Question

I have two tables in a MySQL database, Locations and Tags, and a third table LocationsTagsAssoc which associates the two tables and treats them as a many-to-many relationship.

Table structure is as follows:

Locations
---------
ID int (Primary Key)
Name varchar(128)

LocationsTagsAssoc
------------------
ID int (Primary Key)
LocationID int (Foreign Key)
TagID int (Foreign Key)

Tags
----
ID int (Primary Key)
Name varchar(128)

So each location can be tagged with multiple tagwords, and each tagword can be tagged to multiple locations.

What I want to do is select only Locations which are tagged with all of the tag names supplied. For example:

I want all locations which are tagged with both "trees" and "swings". Location "Park" should be selected, but location "Forest" should not.

Any insight would be appreciated. Thanks!

Daft

Answer 1

There are two ways to do this. I prefer the first way, which is to self-join for each tag:

SELECT l.*
FROM Locations l
JOIN LocationsTagsAssoc a1 ON a1.LocationID = l.ID
JOIN Tags t1 ON a1.TagID = t1.ID AND t1.Name = ?
JOIN LocationsTagsAssoc a2 ON a2.LocationID = l.ID
JOIN Tags t2 ON a2.TagID = t2.ID AND t2.Name = ?
JOIN LocationsTagsAssoc a3 ON a3.LocationID = l.ID
JOIN Tags t3 ON a3.TagID = t3.ID AND t3.Name = ?;

The other way also works, but using GROUP BY in MySQL tends to incur a temporary table and performance is slow:

SELECT l.*
FROM Locations l
JOIN LocationsTagsAssoc a ON a.LocationID = l.ID
JOIN Tags t ON a.TagID = t.ID
WHERE t.Name IN (?, ?, ?)
GROUP BY l.ID
HAVING COUNT(*) = 3;

---

Re comment from @Erikoenig:

If you want to make sure there are no extra tags, you can do it this way:

SELECT l.*
FROM Locations l
JOIN LocationsTagsAssoc a ON a.LocationID = l.ID
JOIN Tags t ON a.TagID = t.ID
GROUP BY l.ID
HAVING COUNT(*) = 3 AND SUM(t.Name IN (?, ?, ?)) = 3;

Taking out the WHERE clause allows other tags to be counted, if there are any. So the COUNT() may be greater than 3.

Or if the count is exactly three tags, but some of these three are not the correct tags, then the SUM() condition in the HAVING clause makes sure that all three tags you want are present in the group.

Seethe

Thanks, Bill. This makes sense and works well. Both queries are about the same speed on a 5 tagname query - I haven't tested any further than that. Excellent answer - thank you. – Daft

I can confirm from experience that although the latter approach looks nicer (to me, anyway), with large tables the first approach is likely to be very much more efficient. – Raeleneraf

@NickF, yes, I found the self-join method to have much better performance. See results in my presentation SQL Query Patterns, Optimized – Seethe

I have the same problem, but would like to search exact matches (i.e. no additional tags). Would adding a WHERE (SELECT COUNT(*) FROM LocationsTagsAssoc WHERE LocationsTagsAssoc.LocationID = l.id) = 3 be sensible? – Xenocryst

Answer 2

You need locations where there doesn't exist a given tag that doesn't appear in the LocationsTagsAssoc table with the location.

You can specify the given tags with IN () as in the following, or by joining onto another table containing them.

I.e.

SELECT l.*
FROM Locations AS l
WHERE NOT EXISTS (
    SELECT NULL FROM Tags AS t
    WHERE NOT EXISTS (
        SELECT NULL FROM LocationsTagsAssoc AS lt
        WHERE lt.LocationId = l.ID
            AND lt.TagID = t.ID
    )
        AND t.ID IN (1, 2, 3,...)
)

Buxton