Tango.info music sql query list: Difference between revisions
Jump to navigation
Jump to search
(38 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
Sublist of [[tango.info sql query list]] | Sublist of [[tango.info sql query list]] | ||
== | ==Track and work== | ||
<pre> | <pre> | ||
SELECT * | SELECT * | ||
Line 26: | Line 26: | ||
</pre> | </pre> | ||
<pre> | <pre> | ||
SELECT | 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 | work_id,track_name,track_genr,track_orch,track_voca,track_date,track_lang,composer,lyricist,dp_a | ||
Line 35: | Line 35: | ||
WHERE | WHERE | ||
t.work_id <> "" | t.work_id <> "" | ||
AND | /* composition free */ | ||
dp_c="C" | AND dp_c="C" | ||
AND | /* (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}$' | AND track_date RLIKE '^[0-9]{4}(-[0-9]{2}){0,2}$' | ||
GROUP BY track_name, | /* 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 | |||
</pre> | |||
==Work having track and having composer with non t.i person code elements== | |||
<pre> | |||
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 | |||
</pre> | |||
<pre> | |||
SELECT * | |||
FROM ti_work | |||
WHERE composer RLIKE '[0-9]' | |||
ORDER BY track_qty DESC | |||
</pre> | |||
<pre> | |||
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 | |||
</pre> | |||
<pre> | |||
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 | |||
</pre> | </pre> | ||
== | ==Work that is a deletion candidate== | ||
<pre> | |||
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)' | |||
</pre> | |||
==Product with no track in t.i but with external product link== | |||
SELECT * | SELECT * | ||
FROM | FROM ti_product p, ti_product_external pe | ||
WHERE | WHERE track_qty =0 | ||
AND | AND p.tinp = pe.tinp | ||
AND | AND pe.site = "xyz" | ||
ORDER BY | ORDER BY p.`product_artist` ASC | ||
==Track duration== | |||
See [[tango.info track duration sql]] | |||
==Work== | ==Work== | ||
Line 68: | Line 172: | ||
WHERE tiwc is NULL | WHERE tiwc is NULL | ||
ORDER BY `ti_track`.`work_id` ASC | 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 |
Latest revision as of 2016-11-12T16:32:49
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