Tango.info track duration sql

tango.info track duration

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