Tango.info sql query list

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

• 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