SQL query to return specific labels if exist (0 if it does not exist)

Question

There are two tables given, tag and media. mysql> select * from media;

+----+---------+----------+
| id | name    | duration |
+----+---------+----------+
| 1  | cat.mp4 | 3.4      |
| 2  | dog.mp4 | 8        |
+----+---------+----------+

mysql> select * from tag;

+----+----------+-------+--------+------------+
| id | media_id | type  | value  | confidence |
+----+----------+-------+--------+------------+
| 1  | 1        | LABEL | cat    | 0.9        |
| 2  | 1        | LABEL | person | 0.6        |
| 3  | 1        | TEXT  | kitty  | 0.95       |
| 4  | 2        | LABEL | dog    | 0.8        |
| 5  | 2        | LABEL | person | 0.75       |
| 6  | 2        | TEXT  | food   | 0.7        |
+----+----------+-------+--------+------------+

I need to get the output table by joining two tables that gives media_id, name, duration and label of the value from tag such that if the value is cat, the confidence of cat will be inserted into label_cat column otherwise 0 will be inserted. Something like this:

+----------+---------+----------+-----------+-----------+--------------+
| media_id | name    | duration | label_cat | label_dog | label_person |
+----------+---------+----------+-----------+-----------+--------------+
| 1        | cat.mp4 | 3.4      | 0.9       | 0         | 0.6          |
| 2        | dog.mp4 | 8        | 0         | 0.8       | 0.75         |
+----------+---------+----------+-----------+-----------+--------------+

Welcome to SO Pankti. Please show what have you tried so far. — Soumendra, Dec 23 '18 at 16:12
@PanktiPatel detailed insights on how to solve a MySQL pivot table [are found here](https://stackoverflow.com/questions/7674786/mysql-pivot-table). Hope it helps. — Jon Jaussi, Dec 23 '18 at 18:11

score 0 · Answer 1 · answered Dec 23 '18 at 16:16

If I understand correctly, you want conditional aggregation:

select m.id, m.name, m.duration,
       max(case when t.value = 'cat' then t.confidence end) as label_cat,
       max(case when t.value = 'dog' then t.confidence end) as label_dog,
       max(case when t.value = 'person' then t.confidence end) as label_person
from media m left join
     tag t
     on m.id = t.media_it
group by m.id, m.name, m.duration

Himanshu · Answer 2 · 2018-12-23T16:51:05.677

0

    Select t.media_id,m.name,m.duration,
     case "label_cat " when t.value ='cat' then 
    t.confidence else 0 end case,
    case "label_dog" when t.value ='dog' then 
    t.confidence else 0 end case,
     case "label_person" when t.value ='person' then 
    t.confidence else 0 end case 
    from
    tag t right join media m on t.id=m.id
   group by t.media_id

edited Dec 23 '18 at 16:51

answered Dec 23 '18 at 16:27

Himanshu

3,830
2
10
29

SQL query to return specific labels if exist (0 if it does not exist)

2 Answers2