自定义连接查询从3表获取记录

Question

I have 3 table in mysql database like following

table 1 : videos

id    name
1     one
2     two
3     three

table 2: tags

id    name
1     stage=test
2     age=under18

table 3 : tags_to_items where tags_id = tags.id and item_id=videos.id

tag_id    item_id
1         1
2         2
2         1
1         3 

Here item_id 1 has 2 tags. I want to select all the records from videos table where tag_id=1 and tag_id=2. So output should be video.id=1.

Please help me to do like that, thanks in advance.

Answer 1

You really don't need any query from videos or join. Just do what you need so far:

SELECT item_id as VIDEO_ID 
   SUM(CASE WHEN tag_id = 1 THEN 1 ELSE 0 END) as tag_1,
   SUM(CASE WHEN tag_id = 2 THEN 1 ELSE 0 END) as tag_2
FROM tags_to items
WHERE tag_id = 1 or tag_id = 2
GROUP BY VIDEO_ID
HAVING tag_1>0 AND tag_2>0