Tango.info track duration sql
Jump to navigation
Jump to search
Combine with track duration table (for data import)
SELECT t.tinp,t.side_num,t.track_num,d.duration FROM ti_track t LEFT JOIN ti_track_duration d ON t.tinp=d.tinp AND t.side_num=d.side AND t.track_num=d.tracknumber WHERE t.tinp RLIKE '^[0-9]{14}$' ORDER BY t.tinp,t.side_num,t.track_num
Count
Count tracks having a duration
SELECT count(*) FROM ti_track t WHERE t.tinp RLIKE '^[0-9]{14}$' AND t.track_dura RLIKE '^[0-9]{2}:[0-9]{2}'
Count tracks having duration and TIWC
SELECT count(*) FROM ti_track t WHERE t.tinp RLIKE '^[0-9]{14}$' AND t.track_dura RLIKE '^[0-9]{2}:[0-9]{2}' AND t.work_id REGEXP '[TZ]{1}[0-9]{10}'
Count tracks having duration, TIWC and date
SELECT count(*) FROM ti_track t WHERE t.tinp REGEXP '^[0-9]{14}$' AND t.track_dura REGEXP '^[0-9]{2}:[0-9]{2}' AND t.work_id REGEXP '[TZ]{1}[0-9]{10}' AND t.track_date REGEXP '(19|20)[0-9]{2}-[0-9]{2}-[0-9]{2}'
Select tracks that have no duration but a discID
SELECT t.tinp, t.side_num, t.track_num , d.cddb_discid FROM ti_track t RIGHT JOIN ti_discid d ON t.tinp = d.tinp AND t.side_num = d.side_num WHERE t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}'
Count track that are not 33 or 78 and have no duration
SELECT count(*) FROM ti_track t WHERE t.tinp REGEXP '^[0-9]{14}$' AND t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' AND t.track_format NOT REGEXP '(33|78)'
Select TINP
Select tinp of products that have track 1 without duration
SELECT t.tinp FROM ti_track t WHERE t.tinp REGEXP '^[0]{1}[0-9]{13}$' AND t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' AND t.track_format NOT REGEXP '(33|78)' AND t.track_num =1 ORDER by t.tinp
SELECT t.tinp, p.product_name FROM ti_track t, ti_product p WHERE t.tinp REGEXP '^[0]{1}[0-9]{13}$' AND t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' AND t.track_format NOT REGEXP '(33|78)' AND t.track_num =1 AND t.tinp =p.tinp ORDER by t.tinp
Select tinp of products that have track 1 without duration but where the track format is not 33 or 78 and the collection is not Coleccion Almacen
SELECT t.tinp FROM ti_track t WHERE t.tinp REGEXP '^[0]{1}[0-9]{13}$' AND t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}' AND t.track_format NOT REGEXP '(33|78)' AND t.track_num =1 AND t.tinp NOT REGEXP '^0248000203' ORDER by t.tinp