MySQL question.
table tags (id, …)
table video (id, …)
table video2tags (videoid, tagsid, …)
Given a number of tag id’s, I am trying to select videos that are tagged with multiple tags.
SELECT video.id FROM video, video2tags WHERE (video.id = video2tags.videoid AND video2tags.tagsid = 89) AND (video.id = video2tags.videoid AND video2tags.tagsid = 88);
This doesn’t seem to work. Any pointers would be really welcome!
February 1st, 2005 at 5:44 pm
I always use the same name, if it is the same thing. So I would do this:
table tags(tag_id, …)
table video (video_id, …)
table video2tags(video_id, tag_id, …)
In MySQL you can than use the keyword USING and do something like:
SELECT v.video_id
FROM video v
INNER JOIN video2tags vt USING(video_id)
INNER JOIN tags t USING(tag_id)
WHERE t.tag_id = 89 AND t.tag_id = 88
otherwise it would be something like this:
SELECT v.id
FROM video v
INNER JOIN video2tags vt ON v.id = vt.videoid
INNER JOIN tags t ON t.id = vt.tagid
WHERE t.id = 89 AND t.id = 88
February 1st, 2005 at 5:45 pm
SELECT v.video_id
FROM video v
INNER JOIN video2tags vt USING(video_id)
INNER JOIN tags t USING(tag_id)
WHERE t.tag_id = 89 AND t.tag_id = 88