Tango.info music sql query list

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 

SELECT *
FROM ti_work
WHERE composer RLIKE '[0-9]'
ORDER BY track_qty DESC

SELECT work_id, track_name, track_orch, track_voca, composer, lyricist, dp_a, dp_c, COUNT( * ) 
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 */

AND (
work_id REGEXP  "[T]"
)

GROUP BY tiwc
ORDER BY COUNT( * ) DESC 

SELECT work_id, track_name, track_orch, track_voca, composer, lyricist, dp_a, dp_c, COUNT( * ) 
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]"
OR lyricist REGEXP  "[0-9]"
)/* group track to performances - which TINT and genre shows up in the results is not defined */
AND (
work_id REGEXP  "[TZ]"
)
GROUP BY tiwc
ORDER BY COUNT( * ) DESC 

Work that is a deletion candidate

SELECT distinct(w.tiwc),w.work_name_orig,
w.composer,w.lyricist,w.comp_type,w.source
FROM ti_work w
LEFT JOIN ti_track t
ON w.work_name_asci = t.track_name_az
WHERE 
w.tiwc RLIKE '^Z099999'

/* track has no tiwc, if it had one 
it would not start with Z099999
and therefore related to another work
therefore it cannot be used as an evidence that 
a Z099999 work might have a track*/
AND (t.work_id = "" OR t.work_id IS NULL)

/* select only work where no track
has the same name */
AND (t.tinp IS NULL)

/* limit genres, exclude works without genre*/
AND w.comp_type NOT RLIKE '(tango|milonga|vals)'

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

SELECT * 
FROM ti_product p, ti_product_external pe
WHERE track_qty =0
AND p.tinp = pe.tinp
AND pe.site =  "xyz"
ORDER BY  p.`product_artist` ASC

Track duration

See tango.info track duration sql

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

Genre track2work

SELECT ti_work.tiwc, track_name_az, track_genr, comp_type, ti_work.track_qty, COUNT( * ) AS MODE_d
FROM ti_track, ti_work, ti_db_stat_by_work
WHERE ti_db_stat_by_work.tiwc = ti_track.work_id
AND ti_work.tiwc = ti_track.work_id
AND ti_work.track_qty >0
AND comp_type =  
AND ti_work.tiwc
RLIKE  '[TZ]'
GROUP BY tiwc
ORDER BY  `ti_track`.`track_genr` ASC

GTIN in product feed

SELECT tinp, gtin14, title, description, product_type
FROM ti_product
RIGHT JOIN x_google_feed_product ON ti_product.tinp = x_google_feed_product.gtin14
WHERE tinp IS NULL
GROUP BY gtin14
ORDER BY `x_google_feed_product`.`gtin14` ASC

Work copyright, person copyright

Works having the composition not marked public domain, and having one composer marked public domain

SELECT ti_work.tiwc, ti_work.work_name_orig, ti_work.composer, ti_work.lyricist 
,ti_person.dp

FROM ti_work

INNER JOIN ti_person_work
ON ti_work.tiwc = ti_person_work.tiwc

RIGHT JOIN ti_person
ON ti_person_work.person_code = ti_person.person_code

WHERE ti_work.dp_c <> 'C' AND ti_person.dp= "DP"  AND ti_person_work.role= "composer"
ORDER BY `ti_work`.`tiwc` ASC

---

SELECT ti_work.tiwc, ti_work.work_name_orig, ti_work.composer, ti_work.lyricist, ti_work.performance_qty, ti_work.track_qty, ti_person.dp

FROM ti_work

INNER JOIN ti_person_work ON ti_work.tiwc = ti_person_work.tiwc

RIGHT JOIN ti_person ON ti_person_work.person_code = ti_person.person_code

WHERE ti_work.dp_c <> 'C' AND ti_person.dp= "DP" AND ti_person_work.role= "composer" AND ti_work.composer NOT RLIKE ','

ORDER BY `ti_work`.`performance_qty` DESC, `ti_work`.`track_qty` DESC