Choose a many-to-many relationship in MySQL

Question

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

The structure of the table 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)

Thus, each place can be tagged with multiple tags, and each of them can be tagged at multiple locations.

What I want to do is select only the locations that are marked with all the tag names indicated . For instance:

I want all locations marked with “trees” and “swings”. You must select the Park location, but the Forest location should not be.

. !

Answer 1

. , :

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 = ?;

, GROUP BY MySQL , :

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;

---

@Erikoenig:

, :

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;

WHERE , . , COUNT() 3.

, , , SUM() HAVING , .

Answer 2

, , LocationsTagsAssoc .

IN(), , , .

..

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,...)
)