SQL query - selection of records based on account conditions

Question

The title may not seem entirely understandable - I 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. Videos are either model videos or not.

Sample data in tables with corresponding column names

Topics

  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 I'm trying to do this for the selected course, select all topics and their corresponding videos. Now, if the topic has more than one video - I want to select a video with is_sample as "N".

As in the example above - topic_id 3 has two videos with video id 13 and 14, so you need to select video_id 13.

This is the request that I still have

  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, if the score is 2 - I want to select an entry with is_sample = 'N' Is this possible, and how can I do it. Thanks so much for your time.

Answer 1

One way to solve this problem is to

• Join the built-in view that calculates counting by topic.
• Use a counter to create a case statement that attaches only to IS_SAMPLE = N

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

Exit

 | 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 |