mysql query question (images,tags)

[Jens Kleikamp]

hi to all,

I have a simple question/szenario.

Here are my tables:

1. image (id, name)
2. tag (id, name)
3. images_tags (image_id, tag_id)

At the moment I have the following working query, it selects all images 
which have *at least one of the tag ids* (25,30) assigned.


SELECT DISTINCT image.id, image.label
FROM `image`
JOIN `images_tags`
ON image.id = images_tags.image_id  images_tags.tag_id IN (25,30)


Now my plan is to adjust the query so that only images are selected 
which have *all the tags assigned*, so the IN command in the ON clause 
does not fit anymore. Here is my attempt to replace the IN:



SELECT DISTINCT image.id, image.label
FROM `image`
JOIN `images_tags`
ON image.id = images_tags.image_id 
(
   images_tags.tag_id = 25
   
   images_tags.tag_id = 30
)


But it doesnt´t work :(


It would be awesome if somebody could help me.

thanks a lot!

-jens


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql query question (images,tags)

[Jens Kleikamp]

Jens Kleikamp schrieb:

hi to all,

I have a simple question/szenario.

Here are my tables:

1. image (id, name)
2. tag (id, name)
3. images_tags (image_id, tag_id)

At the moment I have the following working query, it selects all images 
which have *at least one of the tag ids* (25,30) assigned.


SELECT DISTINCT image.id, image.label
FROM `image`
JOIN `images_tags`
ON image.id = images_tags.image_id  images_tags.tag_id IN (25,30)


Now my plan is to adjust the query so that only images are selected 
which have *all the tags assigned*, so the IN command in the ON clause 
does not fit anymore. Here is my attempt to replace the IN:



SELECT DISTINCT image.id, image.label
FROM `image`
JOIN `images_tags`
ON image.id = images_tags.image_id 
(
   images_tags.tag_id = 25
   
   images_tags.tag_id = 30
)


Solution #1:

SELECT fgl_image.id, fgl_image.label
FROM fgl_image, fgl_images_tags A, fgl_images_tags B
WHERE fgl_image.id = A.image_id
AND fgl_image.id = B.image_id
AND A.tag_id =10
AND B.tag_id =9

Solution #2:

SELECT fgl_image.id, fgl_image.label
FROM fgl_image
INNER JOIN fgl_images_tags ON fgl_images_tags.image_id = fgl_image.id
AND fgl_images_tags.tag_id
IN ( 10, 9 )
GROUP BY fgl_image.id, fgl_image.label
HAVING COUNT( * ) =2


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]