how can add an extra select in this query?

Posted by BulgedSnowy on Stack Overflow See other posts from Stack Overflow or by BulgedSnowy
Published on 2010-05-20T02:48:41Z Indexed on 2010/05/20 2:50 UTC
Read the original article Hit count: 197

Filed under:
|

i've three tables related.

images: id | filename | filesize | ...

nodes: image_id | tag_id

tags: id | name

And i'm using this query to search images containing x tags

SELECT images.* FROM images 
INNER JOIN nodes ON images.id = nodes.image_id 
WHERE tag_id IN (SELECT tags.id FROM tags WHERE tags.tag IN ("tag1","tag2")) 
GROUP BY images.id HAVING COUNT(*)= 2

The problem is that i need to retrieve also all images contained by the retrieved image, and i need this in the same query.

This the actual query wich search retrieve all tags contained by the image:

SELECT tag FROM nodes 
JOIN tags ON nodes.tag_id = tags.id 
WHERE image_id = images.id and nodes.private = images.private 
ORDER BY tag

How can i mix this two to have only one query?

© Stack Overflow or respective owner

Related posts about sql

Related posts about join