Joining 3 tables efficiently in MYSQL

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.

I've got two questions, one pretty simple (I think!), and one more difficult.

Simple question

Given a place_id, how can I list all the tags that have image in that place_id? An example would be:

Given place_id = 7, return tagA, tagB.

Hard question

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 

Would this be better to do in PHP by performing mul开发者_运维百科tiple queries, or can I do this using MYSQL?

1. SELECT i.place_id, t.name as tag_name
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 = 7

2. 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

It's always better to avoid multiple queries if possible.
*Be aware that the maximum length of GROUP_CONCAT is controlled by group_concat_max_len variable. If you are expecting to get a long list of concatenated strings, you might need to change the default value.

UPDATE
If you want the queries to display images with no tags associated, change INNER JOINs to LEFT JOINs

Simple Question

SELECT name 
FROM images 
RIGHT JOIN tagsToImages on images.id = tagsToImages.image_id 
RIGHT JOIN tags ON tags.tag_id = tagsToImages.image_id 
WHERE place_id = '7'

For simple question, use join which will be more efficient:

select
    t.name as `Tag`
from
    images i
left join
    tagsToImages ti on ti.image_id = i.id
left join
    tags t on t.id = ti.tag_id
where
    place_id = $place_id

For hard question, see GROUP_CONCAT: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

issuing a single query with join is better than adding loops, multiple datastructures and manually comparing the values in PHP.