Relating categories with tags using SQL

Question

I want be able to find tags of items under the a certain category. Following is example of my database design:

      images
      +----------+-----+-------------+-----+
      | image_id | ... | category_id | ... |
      +----------+-----+-------------+-----+
      |    1     | ... |     11      | ... |
      +----------+-----+-------------+-----+
      |    2     | ... |     12      | ... |
      +----------+-----+-------------+-----+
      |    3     | ... |     11      | ... |
      +----------+-----+-------------+-----+
      |    4     | ... |     11      | ... |
      +----------+-----+-------------+-----+

      images_tags
      +----------+--------+
      | image_id | tag_id |
      +----------+--------+
      |     1    |   53   |
      +----------+--------+
      |     3    |   54   |
      +----------+--------+
      |     2    |   55   |
      +----------+--------+
      |     1    |   56   |
      +----------+--------+
      |     4    |   57   |
      +----------+--------+

tags and categories each have their own table relating the id to an actual name(text). So my question is how will i find out that images with category_id=11 have have the tag_id 53 54 56 57.

In other words how to find the tags that images in certain category have?

Answer 1

try this:

SELECT images.*, tags.* from images, tags where images.image_id = images_tags.image_id and  images.category_id = X;

Where X is the desired category.

Answer 2

Maybe an inner SELECT would do it? Something like:

 SELECT tag_id FROM images_tags WHERE image_id IN (SELECT image_id FROM images WHERE cateory_id = 11 )