Tango.info track duration sql
Appearance
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 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
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 NOT REGEXP '^0248000203'
AND t.tinp =p.tinp
ORDER by
t.tinp
SELECT t.tinp, p.product_name, p.product_artist, p.product_collection
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 NOT REGEXP '^0248000203'
AND t.tinp = p.tinp
ORDER BY t.tinp
LIMIT 0 , 200