Joining 3 tables in MYSQL and filtering

This is a follow up of my previous question, but is slightly different and merits it's own question.

I have 3 tables like this:

images (~10,000 rows)

id | place_id | filename 
---|----------|----------
 1 |     4    |  abc.jpg
 2 |     3    |  def.jpg
 3 |     4    |  ghi.jpg
 4 |     7    |  jkl.jpg

tags (~100 rows)

id |   name   |
---|----------|
 1 |   tagA   |
 2 |   tagB   |
 3 |   tagC   |
 4 |   tagD   |

tagsToImages (~30,000 rows)

id |  tag_id  | image_id
---|----------|----------
 1 |     1    |    4
 2 |     3    |    2
 3 |     2    |    4
 4 |     1    |    1

As an example, the last table shows that the tag with id = 1 is linked with the image with id = 4.

What I'd like to do is to select all the images with a certain place_id, and associate each image with a list of tags like so:

Select all with place_id = 4, whilst joining to tags info.

 filename |    tags    
-----------------------
  abc.jpg | tagA       
  ghi.jpg | tagA, tagB 

A query to do that was suggested by a1ex07, and looks like this:

SELECT i.filename, GROUP_CONCAT(t.name SEPARATOR ',') AS tags
FROM images i
INNER JOIN tagsToImages tti ON (tti.image_id = i.id)
INNER JOIN tags t ON (t.id = tti.tag_id)
WHERE i.place_id = 4 GROUP BY i.filename

This is fantastic, and works really well. What I'd like to do now is to filter that set of results by specifying a tag as well as a place.

My attempt of:

SELECT i.filename, GROUP_CONCAT(t.name SEPARATOR ',') AS tags
FROM images i
INNER JOIN tagsToImages tti ON (tti.image_id = i.id)
INNER JOIN tags t ON (t.id = tti.tag_id)
WHERE i.place_id = 4 AND t.id = 3 GROUP BY i.filename

Almost works, but it only includes images that exclusively have tag with id 3, not images with lots of tags, one of which has id 3.

Can a开发者_JS百科ny one help me out?

Try putting a subquery in the WHERE clause to select any images that have tag_id of 3 e.g.

WHERE i.place_id = 4 AND i.imageID IN 
(
    SELECT image_id 
    FROM tagsToImages 
    WHERE tag_id = 3
)

Note - haven't tried this for real yet - no database on this PC to test with. Will check and update later

I've tested Kris C's answer on MySQL 5.0 and it worked for my test database:

SELECT i.filename, GROUP_CONCAT(t.name SEPARATOR ',') AS tags
FROM images i
INNER JOIN tagsToImages tti ON (tti.image_id = i.id)
INNER JOIN tags t ON (t.id = tti.tag_id)
WHERE i.place_id = 4 AND i.id IN 
(
    SELECT image_id 
    FROM tagsToImages 
    WHERE tag_id = 1
) GROUP BY i.filename;

Which resulted in (based on the data you gave in the question):

+----------+------+
| filename | tags |
+----------+------+
| abc.jpg  | tagA |
+----------+------+

You said that this query doesn't work for you, so can you please say which database you are running and with which specific data this is not working?