That query doesn't account for all the other parameters that could have been used in the inital Media query. A typical media collection's SQL would look like:allspiritseve wrote:Code: Select all
SELECT rt.* FROM tags AS lt INNER JOIN media_tags AS lmt ON (lt.id = lmt.tag_id) INNER JOIN media_tags AS rmt ON (lmt.media_id = rmt.media_id) INNER JOIN tags AS rt ON (rmt.tag_id = rt.id) WHERE rt.id IN (5, 16)
Code: Select all
SELECT m.id AS id FROM media m
INNER JOIN media_tags mt ON m.id=mt.media_id AND mt.tag_id IN (15,6)
WHERE roll_id=5 AND year(date)>1999
GROUP BY m.id HAVING count(*)=2 ORDER BY id
And this is just for columns of the media table. The Media query could also have joined other, as-of-yet unmentioned tables as well.
Code: Select all
SELECT m.id AS id FROM media m
LEFT JOIN users u ON m.user_id=u.id
INNER JOIN media_tags mt ON m.id=mt.media_id AND mt.tag_id IN (15,6)
WHERE roll_id=5 AND year(date)>1999 AND u.username='inghamn'
GROUP BY m.id HAVING count(*)=2 ORDER BY id
Using the complete media SQL as a subquery seemed a whole lot easier.
Code: Select all
SELECT DISTINCT
t.id AS id
FROM
tags t
JOIN media_tags mt ON t.id=mt.tag_id
JOIN (
SELECT m.id AS id FROM media m
INNER JOIN media_tags mt ON m.id=mt.media_id AND mt.tag_id IN (15,6)
WHERE roll_id=5 AND year(date)>1999
GROUP BY m.id HAVING count(*)=2 ORDER BY id
)related_media_ids ON mt.media_id = related_media_ids.media_id
Once we've got a collection, and we want some other collection to be an INTERSECT of the first collection, we have to reverse-engineer the first collection somehow....or just do the subquery.