Have Geek Brain

How do I add an extra selection to this query?

I have three tables.

images: id | file name | files | ...

nodes: image_id | tag_id

tags: id | name

And I am using this query to find images that contain 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, I need to get all the tags contained in the resulting image as well, and I need that in the same request.

This actual query, which is being searched, retrieves all the tags contained in 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 the two to only have one request?

I want the whole image table in the results plus the image tags. Similar: I would file name file size tags

Perhaps if possible concat tags.

+2


a source to share


2 answers


Is something like this possible?



SELECT i.id, t.name
FROM images i
    INNER JOIN nodes n ON n.image_id = i.id
    INNER JOIN tags t ON t.id = n.tag_id
WHERE i.id IN
(
    SELECT nodes.image_id 
    FROM nodes
        INNER JOIN tags ON tags.id = nodes.tag_id
    WHERE tags.name IN ('tag1', 'tag2')
)
+1


a source


And this?



SELECT tag FROM nodes 
JOIN tags ON nodes.tag_id = tags.id 
WHERE image_id = images.id 
AND nodes.private = images.private 
AND image_id in (
   SELECT images.id 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)
ORDER BY tag
0


a source






More articles:


All Articles