Select in a many-to-many relationship in MySQL
- by Joff Williams
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!
