Avinash Mulewa Avinash Mulewa - 2 months ago 7
SQL Question

sqlite join query iPad

I have three tables

Personal_video
+------------------------------+
|presonal_video_id | title |
----------------------------
1 | test1|
2 | test2|
3 | test3|
4 | test4|

personal_video_tags
+------------------------------+
|tag_id | tag_title |
----------------------------
1 | august|
2 | 2016 |
3 | 2015 |
4 | 2014 |


personal_video_tag_mapping
+------------------------------+
|tag_id | presonal_video_id |
----------------------------
1 | 1 |
2 | 2 |
3 | 3 |
4 | 1 |


Now i want to write a query which will return me the videos on the basis of common tags like if user select tag "August" & "2014" then the query should return videos which is connected to both the tags.

currently my query is

SELECT presonal_video_id,title
FROM personal_video
WHERE presonal_video_id IN
(
SELECT personal_video_id AS PID
FROM personal_video_tag_mapping
WHERE tag_id IN ("1","2") AND privacy_level != 2
GROUP BY personal_video_id
HAVING COUNT( PID ) > 1
)


It is giving me write result but when there is large data then it takes long time. Can someone teel me correct way to write this query

Thank You in advance

Answer

Try this query:

SELECT t1.presonal_video_id, t1.title 
FROM personal_video AS t1
JOIN personal_video_tag_mapping AS t2 
    ON t1.presonal_video_id = t2.presonal_video_id
JOIN personal_video_tags AS t3
    ON t2.tag_id = t3.tag_id    
WHERE t3.tag_title IN ('august', '2014')
GROUP BY t1.presonal_video_id, t1.title 
HAVING COUNT(*) = 2
Comments