Tango.info music sql query list
Jump to navigation
Jump to search
Sublist of tango.info 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 tinp,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.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 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 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
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