Tango.info music sql query list

track and work

SELECT *
FROM ti_track t
LEFT JOIN ti_work w
ON
t.work_id=w.tiwc
WHERE t.work_id <>  ""

Track and work - no work rights

SELECT *
FROM ti_track t
LEFT JOIN ti_work w
ON
t.work_id=w.tiwc
WHERE
t.work_id <> ""
AND 
dp_c="C"
AND 
 ( track_lang="zxx" OR dp_a ="A")

SELECT product_tin,product_side,track_num,track_format,
work_id,track_name,track_genr,track_orch,track_voca,track_date,track_lang,composer,lyricist,dp_a

FROM ti_track t
LEFT JOIN ti_work w
ON
t.work_id=w.tiwc
WHERE
t.work_id <> ""
/* composition free */
AND dp_c="C"
/* (no language) or (text free) */
AND ( track_lang="zxx" OR dp_a ="A") 

/* only YYYY, YYYY-MM, YYYY-MM-DD */
AND track_date RLIKE '^[0-9]{4}(-[0-9]{2}){0,2}$'

/* 50 year copyright term for sound recordings 
may be higher in some jurisdictions
*/
AND track_date < 1962

/* group track to performances - 
 which TINT and genre shows up in the results is not defined */
GROUP BY track_name,track_orch, track_voca,track_date,track_lang

Work having track and having composer with non t.i person code elements

SELECT work_id, track_name, track_orch, track_voca, composer, lyricist, dp_a, dp_c
FROM ti_track t
LEFT JOIN ti_work w ON t.work_id = w.tiwc
WHERE t.work_id <>  ""/* composition free */#AND dp_c="C"
/* (no language) or (text free) */#AND ( track_lang="zxx" OR dp_a ="A") 
/* only YYYY, YYYY-MM, YYYY-MM-DD */
AND track_date
RLIKE  '^[0-9]{4}(-[0-9]{2}){0,2}$'/* 50 year copyright term for sound recordings may be higher in some jurisdictions */#AND track_date < 1962

AND (
composer
REGEXP  "[0-9]"
)/* group track to performances - which TINT and genre shows up in the results is not defined */
GROUP BY tiwc
ORDER BY composer ASC , lyricist ASC

Product with no track in t.i but with external product link

SELECT * 
FROM ti_product p, ti_product_external pe
WHERE c_tracks =0
AND p.album_tin = pe.tin
AND pe.site =  "xyz"
ORDER BY  p.`album_artist` ASC

Track duration

Combine with track duration table (for data import)

SELECT t.product_tin,t.product_side,t.track_num,d.duration
FROM ti_track t
LEFT JOIN ti_track_duration d
ON
t.product_tin=d.tinp
AND
t.product_side=d.side
AND
t.track_num=d.tracknumber
WHERE t.product_tin  RLIKE '^[0-9]{14}$'
ORDER BY
t.product_tin,t.product_side,t.track_num

Count tracks having a duration

SELECT count(*)
FROM ti_track t
WHERE t.product_tin  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.product_tin  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.product_tin  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.product_tin, t.product_side, t.track_num , p.product_freedb
FROM ti_track t
LEFT JOIN ti_product p
ON
t.product_tin = p.product_tin
WHERE
t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}'
AND
p.product_freedb REGEXP BINARY '^[a-z]{3}'

Count track that are not 33 or 78 and have no duration

SELECT count(*)
FROM ti_track t
WHERE 
t.product_tin  REGEXP '^[0-9]{14}$' 
AND
t.track_dura NOT REGEXP '^[0-9]{2}:[0-9]{2}'
AND
t.track_format NOT REGEXP  '(33|78)'

Work

SELECT *
FROM ti_work_x_cjjsetblog_unique_tiwc
LEFT JOIN  ti_work
ON
ti_work_x_cjjsetblog_unique_tiwc.tiwc=ti_work.tiwc

show tiwc present in ti_track but not in ti_work

SELECT DISTINCT(work_id),tiwc
FROM ti_track
LEFT JOIN  ti_work
ON
ti_track.work_id=ti_work.tiwc 
WHERE tiwc is NULL
ORDER BY `ti_track`.`work_id` ASC