The title may not seem very clear - was not sure how to explain the problem in one line.
I have 3 tables
TOPIC VIDEO TOPIC_VIDEO
A topic can have one or two videos. The videos are either sample videos or not.
Sample data in the tables with relevant column names
TOPIC
TOPIC_ID | NAME | COURSE_ID 1 | Excel - Add/Subtract | 1 2 | Excel - sort | 1 3 | Excel - filter | 1 4 | Excel - formulas | 1 VIDEO VIDEO_ID | VIDEO_URL 10 | www.youtube.com?v=123 12 | www.youtube.com?v=345 13 | www.youtube.com?v=567 14 | www.youtube.com?v=879 15 | www.youtube.com?v=443 TOPIC_VIDEO TOPIC_VIDEO_ID | TOPIC_ID | VIDEO_ID | IS_SAMPLE 1 | 1 | 10 | Y 2 | 2 | 12 | N 3 | 3 | 13 | N 4 | 3 | 14 | Y 5 | 4 | 15 | N
So what I am trying to do is for a given course select all topics and their corresponding videos. Now if a topic has more than one video - I want to select the video with is_sample as "N".
Like in the above example - topic_id 3 has two videos with video_id 13 and 14 - so the video_id 13 should be selected.
This is the query I have so far
select topic.*,count(topic.topic_id),video.video_id,topic_video.is_sample from topic left join topic_video ON topic_video.topic_id = topic.topic_id left join video ON video.video_id = topic_video.video_id where course_id=1 group by topic.topic_id
So now if the count is 2 - I want to select the record with is_sample='N' Is this possible and how can I accomplish this. Thanks a lot for your time
One way to solve this is to
SELECT *
FROM topic
LEFT JOIN (SELECT topic_id,
Count(topic_id) t_count
FROM TOPIC_VIDEO
GROUP BY topic_id) t
ON topic.topic_id = t.topic_id
LEFT JOIN topic_video
ON topic_video.topic_id = topic.topic_id
AND TOPIC_VIDEO.IS_SAMPLE = CASE
WHEN t.t_count = 2 THEN 'N'
ELSE TOPIC_VIDEO.IS_SAMPLE
END
LEFT JOIN video
ON video.video_id = topic_video.video_id
DEMO
Output
| TOPIC_ID | NAME | COURSE_ID | T_COUNT | TOPIC_VIDEO_ID | VIDEO_ID | IS_SAMPLE | VIDEO_URL |
-------------------------------------------------------------------------------------------------------------------------
| 1 | Excel - Add/Subtract | 1 | 1 | 1 | 10 | Y | www.youtube.com?v=123 |
| 2 | Excel - sort | 1 | 1 | 2 | 12 | N | www.youtube.com?v=345 |
| 3 | Excel - filter | 1 | 2 | 3 | 13 | N | www.youtube.com?v=567 |
| 4 | Excel - formulas | 1 | 1 | 5 | 15 | N | www.youtube.com?v=443 |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With