Get the value in the table that some column is null in MYSQL

Question

Video
-------
id
source_low
source_med
source_high

Source
--------
id
duration
thumbnail

The video item can contain at least 1 or at most 3 of the source (source low, source med, source high).

The idea is same as youtube, that means a video record can contain several quality. So, when I return the duration / thumbnail I would like to check which source is exist and return one of them.

For example , if a video has source_low or source_med , that means there is duration & thumbnail for source_low and source_med, then I just need to return either one of it.

Codeigniter syntax:

  $this->db->select('v.*, sl.duration, sm.duration as duration_m, sh.duration as duration_h, sl.thumbnail, sm.thumbnail as thumbnail_m, sh.thumbnail as thumbnail_h');
        $this->db->from('video as v');
        $this->db->join('source as sl', 'v.source_low = sl.video_id', 'left');
        $this->db->join('source as sm', 'v.source_med = sm.video_id', 'left');
        $this->db->join('source as sh', 'v.source_high = sh.video_id', 'left');
        $this->db->group_by('v.id');

SQL (thumbnail is the same way to get):

SELECT v.*, sl.duration, sm.duration as duration_sm, sh.duration as duration_sh
    FROM video as v
    LEFT JOIN source as sl ON (v.source_low = sl.video_id)
    LEFT JOIN source as sm ON (v.source_med = sm.video_id)
    LEFT JOIN source as sh ON (v.source_high = sh.video_id)
    GROUP BY v.id;

Right now I can return all the result , including the null value, but how can I return only one duration and one thumbnail?

Thanks for helping

Update:

Just update the query but it seems codeigniter has some error in handle it, any idea? Thanks

My code:

$this->db->select('v.*, COALESCE(b.duration, bt.duration, bc.duration) AS duration, COALESCE(b.thumbnail, bt.thumbnail, bc.thumbnail) AS thumbnail');

And in SQL:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS duration, COALESCE(b.thumbnail, `bt`.`thumbnail`, `bc`.`thumbnail)` AS thumbn' at line 1

SELECT `v`.*, `t`.`name`, `t`.`name_tw`, `t`.`name_cn`, COALESCE(b.duration, `bt`.`duration`, `bc`.`duration)` AS duration, COALESCE(b.thumbnail, `bt`.`thumbnail`, `bc`.`thumbnail)` AS thumbnail FROM (`video` as v) JOIN `teacher` as t ON `v`.`teacher_id` = `t`.`id` LEFT JOIN `brightcove` as b ON `v`.`video` = `b`.`video_id` LEFT JOIN `brightcove` as bt ON `v`.`video_tw` = `bt`.`video_id` LEFT JOIN `brightcove` as bc ON `v`.`video_cn` = `bc`.`video_id` WHERE `v`.`is_delete` = 0 AND `v`.`type` = 0 GROUP BY `v`.`id` ORDER BY `v`.`start_date` desc

Answer 1

You can use COALESCE()function to return first non-null value. See MySQL manual for details. E.g:

SELECT v.*, COALESCE(sl.duration, sm.duration, sh.duration) AS duration
    FROM video as v
    LEFT JOIN source as sl ON (v.source_low = sl.video_id)
    LEFT JOIN source as sm ON (v.source_med = sm.video_id)
    LEFT JOIN source as sh ON (v.source_high = sh.video_id)
    GROUP BY v.id;