Tango.info sql query list: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
(→Video) |
||
(23 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
==Various== | |||
<pre> | <pre> | ||
SELECT * | SELECT * | ||
Line 9: | Line 10: | ||
LEFT JOIN tango_persons ON tango_persons.person_code = tango_persons_x_pascale.code | LEFT JOIN tango_persons ON tango_persons.person_code = tango_persons_x_pascale.code | ||
SELECT e.tin, e.comment | |||
FROM tango_products_external e | |||
LEFT OUTER JOIN tango_discs d ON e.tin = d.disc_tin | |||
WHERE d.disc_tin IS NULL | |||
LIMIT 0 , 100 | |||
</pre> | |||
PROBLEM: without last line i get some NULL for unlocode column. i want only these. but the last line makes it that i get no results. | |||
<pre> | |||
SELECT dbtango.tango_cities.unlocode, page_title | |||
FROM dbwiki.mul_page | |||
LEFT JOIN dbtango.tango_cities ON dbtango.tango_cities.unlocode = dbwiki.mul_page.page_title | |||
WHERE `page_namespace` = 14 AND page_title REGEXP '^[A-Z]{5}$' | |||
AND unlocode NOT REGEXP '^[A-Z]{5}$' | |||
</pre> | |||
OK: | |||
<pre> | |||
SELECT page_title | |||
FROM mul_page, mul_categorylinks | |||
WHERE 1 | |||
AND mul_page.page_id = mul_categorylinks.cl_from | |||
AND mul_page.page_namespace =0 | |||
AND mul_categorylinks.cl_sortkey = "*" | |||
AND mul_categorylinks.cl_to | |||
REGEXP '^[A-Z]{5}$' | |||
</pre> | |||
group by | |||
<pre> | |||
SELECT * , count( tango_tracks.album_tin ) as c_tracks2 | |||
FROM tango_albums | |||
LEFT JOIN tango_tracks ON tango_albums.album_tin = tango_tracks.album_tin | |||
WHERE album_collection LIKE '$col' | |||
GROUP BY tango_tracks.album_tin | |||
</pre> | </pre> | ||
==wiki article datetime== | ==wiki article datetime== | ||
SELECT page_title,cl_to | |||
FROM mul_page, mul_categorylinks | |||
WHERE mul_page.page_id = mul_categorylinks.cl_from | |||
AND mul_categorylinks.cl_to RLIKE '[A-Z]{5}' | |||
AND mul_page.page_title | |||
RLIKE '^(19|20)[0-9]{2}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])--[0-9]{2}.*_Festival' | |||
ORDER BY page_title | |||
===task=== | ===task=== | ||
from | from | ||
Line 27: | Line 73: | ||
TRUNCATE TABLE `tango_wiki_pages` ; | TRUNCATE TABLE `tango_wiki_pages` ; | ||
INSERT INTO `tango_wiki_pages` SET page_id = 1 | INSERT INTO `tango_wiki_pages` SET page_id = 1 | ||
[to be completed!] | |||
[[User:Chrisjjj|Chrisjjj]] To convert <tt>page_title</tt> to <tt>page_id</tt>, <tt>time_start</tt>, <tt>time_end</tt> you could use PHP preg_match(). | |||
==Music== | |||
* [[tango.info music sql query list]] | |||
==Video== | |||
===Video id in track table and not in video table=== | |||
SELECT ti_video_track.provider_vid, | |||
ti_video_track.music_lang, | |||
ti_video.provider AS providerX, | |||
ti_video_track.provider AS providerY, | |||
ti_video.provider_vid AS provider_vid | |||
FROM ti_video | |||
RIGHT JOIN ti_video_track | |||
ON ti_video.provider = ti_video_track.provider | |||
AND ti_video.provider_vid = ti_video_track.provider_vid | |||
WHERE 1 AND ti_video.provider IS NULL LIMIT 5000 |
Latest revision as of 2016-09-24T16:13:26
Various
SELECT * FROM tango_persons_x_pascale , tango_persons WHERE tango_persons.person_code = tango_persons_x_pascale.code SELECT * FROM tango_persons_x_pascale LEFT JOIN tango_persons ON tango_persons.person_code = tango_persons_x_pascale.code SELECT e.tin, e.comment FROM tango_products_external e LEFT OUTER JOIN tango_discs d ON e.tin = d.disc_tin WHERE d.disc_tin IS NULL LIMIT 0 , 100
PROBLEM: without last line i get some NULL for unlocode column. i want only these. but the last line makes it that i get no results.
SELECT dbtango.tango_cities.unlocode, page_title FROM dbwiki.mul_page LEFT JOIN dbtango.tango_cities ON dbtango.tango_cities.unlocode = dbwiki.mul_page.page_title WHERE `page_namespace` = 14 AND page_title REGEXP '^[A-Z]{5}$' AND unlocode NOT REGEXP '^[A-Z]{5}$'
OK:
SELECT page_title FROM mul_page, mul_categorylinks WHERE 1 AND mul_page.page_id = mul_categorylinks.cl_from AND mul_page.page_namespace =0 AND mul_categorylinks.cl_sortkey = "*" AND mul_categorylinks.cl_to REGEXP '^[A-Z]{5}$'
group by
SELECT * , count( tango_tracks.album_tin ) as c_tracks2 FROM tango_albums LEFT JOIN tango_tracks ON tango_albums.album_tin = tango_tracks.album_tin WHERE album_collection LIKE '$col' GROUP BY tango_tracks.album_tin
wiki article datetime
SELECT page_title,cl_to FROM mul_page, mul_categorylinks WHERE mul_page.page_id = mul_categorylinks.cl_from AND mul_categorylinks.cl_to RLIKE '[A-Z]{5}' AND mul_page.page_title RLIKE '^(19|20)[0-9]{2}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])--[0-9]{2}.*_Festival' ORDER BY page_title
task
from
dbwiki.mul_page : page_id, page_title
fill
dbtango.tango_wiki_pages : page_id, time_start, time_end
where page_title can be like
- 2007-10-12--14_Festival_Karlsruhe
- 2007-05-30--06-03_Festival_Sydney
- 2007-12-07--2008-01-06_Festival_Roma
solution
TRUNCATE TABLE `tango_wiki_pages` ; INSERT INTO `tango_wiki_pages` SET page_id = 1 [to be completed!]
Chrisjjj To convert page_title to page_id, time_start, time_end you could use PHP preg_match().
Music
Video
Video id in track table and not in video table
SELECT ti_video_track.provider_vid, ti_video_track.music_lang, ti_video.provider AS providerX, ti_video_track.provider AS providerY, ti_video.provider_vid AS provider_vid FROM ti_video RIGHT JOIN ti_video_track ON ti_video.provider = ti_video_track.provider AND ti_video.provider_vid = ti_video_track.provider_vid WHERE 1 AND ti_video.provider IS NULL LIMIT 5000