Tango.info sql query list
Jump to navigation
Jump to search
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
track durations
SELECT tango_tracks.album_tin,tango_tracks.album_side, tango_tracks.track_num , tint, 'durations' FROM `tango_tracks_durations` RIGHT JOIN tango_tracks ON tango_tracks_durations.tint = tango_tracks.album_tin + '-' + tango_tracks.album_side + '-' + tango_tracks.track_num
join tracks and works
SELECT t.work_id, w.source, w.iswc FROM tango_tracks t LEFT JOIN tango_works w ON t.work_id=w.source WHERE t.work_id RLIKE 'sadaic.[0-9]{2,}'
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().
Albums with no track in t.i but with product link
SELECT * FROM tango_albums, tango_products_external WHERE c_tracks =0 AND tango_albums.album_tin = tango_products_external.tin AND tango_products_external.site = "xyz" ORDER BY `tango_albums`.`album_artist` ASC
Work
SELECT * FROM ti_work_x_cjjsetblog_unique_tiwc LEFT JOIN ti_work ON ti_work_x_cjjsetblog_unique_tiwc.tiwc=ti_work.tiwc