Test query a
SELECT t0.page_id AS `page_id`,t1.value AS `v1`,t2.value AS `v2`,t3.value AS `v3` FROM (SELECT t.page_id,p.path_parent,t.value FROM `visualdata_text` `t` JOIN `visualdata_props` `p` ON ((p.id=t.prop_id)) WHERE p.schema_id = '3' AND p.path_no_index = 'name' ) `t0` LEFT JOIN (SELECT t.page_id,p.path_parent,t.value FROM `visualdata_text` `t` JOIN `visualdata_props` `p` ON ((p.id=t.prop_id)) WHERE p.schema_id = '3' AND p.path_no_index = 'states/name' ) `t1` ON (((t1.page_id=t0.page_id))) LEFT JOIN (SELECT t.page_id,p.path_parent,t.value FROM `visualdata_text` `t` JOIN `visualdata_props` `p` ON ((p.id=t.prop_id)) WHERE p.schema_id = '3' AND p.path_no_index = 'states/cities/name' ) `t2` ON (((t2.page_id=t0.page_id) AND (LOCATE( t1.path_parent, t2.path_parent ) = 1))) LEFT JOIN (SELECT t.page_id,p.path_parent,t.value FROM `visualdata_numeric` `t` JOIN `visualdata_props` `p` ON ((p.id=t.prop_id)) WHERE p.schema_id = '3' AND p.path_no_index = 'states/cities/latitude' ) `t3` ON (((t3.page_id=t0.page_id) AND (t3.path_parent = t2.path_parent))) JOIN `page` `page_alias` ON ((page_alias.page_id = t0.page_id)) WHERE ((t0.value = 'Canada')) AND ((t1.value LIKE 'Q%' ESCAPE '`')) LIMIT 50
page title | State name | City name | Latitude |
---|---|---|---|
Data:Countries/Canada | Quebec | Abitibi-Témiscamingue | 48.10018 |
Data:Countries/Canada | Quebec | Acton Vale | 45.65007 |
Data:Countries/Canada | Quebec | Adstock | 46.05007 |
Data:Countries/Canada | Quebec | Albanel | 48.88324 |
Data:Countries/Canada | Quebec | Alma | 48.55009 |
Data:Countries/Canada | Quebec | Amos | 48.56688 |
Data:Countries/Canada | Quebec | Amqui | 48.46382 |
Data:Countries/Canada | Quebec | Ange-Gardien | 45.35008 |
Data:Countries/Canada | Quebec | Asbestos | 45.76678 |
Data:Countries/Canada | Quebec | Baie-Comeau | 49.21679 |
Data:Countries/Canada | Quebec | Baie-D'Urfé | 45.41397 |
Data:Countries/Canada | Quebec | Baie-Saint-Paul | 47.44109 |
Data:Countries/Canada | Quebec | Barraute | 48.43349 |
Data:Countries/Canada | Quebec | Bas-Saint-Laurent | 48.0503 |
Data:Countries/Canada | Quebec | Beaconsfield | 45.43341 |
Data:Countries/Canada | Quebec | Beauceville | 46.21785 |
Data:Countries/Canada | Quebec | Beauharnois | 45.31341 |
Data:Countries/Canada | Quebec | Beaupré | 47.04428 |
Data:Countries/Canada | Quebec | Bécancour | 46.34106 |
Data:Countries/Canada | Quebec | Bedford | 45.11678 |
Data:Countries/Canada | Quebec | Beloeil | 45.56839 |
Data:Countries/Canada | Quebec | Berthierville | 46.08336 |
Data:Countries/Canada | Quebec | Blainville | 45.66678 |
Data:Countries/Canada | Quebec | Bois-des-Filion | 45.66678 |
Data:Countries/Canada | Quebec | Boisbriand | 45.61678 |
Data:Countries/Canada | Quebec | Bonaventure | 48.04573 |
Data:Countries/Canada | Quebec | Boucherville | 45.59104 |
Data:Countries/Canada | Quebec | Breakeyville | 46.68037 |
Data:Countries/Canada | Quebec | Bromont | 45.31678 |
Data:Countries/Canada | Quebec | Brossard | 45.45008 |
Data:Countries/Canada | Quebec | Brownsburg-Chatham | 45.68342 |
Data:Countries/Canada | Quebec | Buckingham | 45.58563 |
Data:Countries/Canada | Quebec | Cabano | 47.68065 |
Data:Countries/Canada | Quebec | Cacouna | 47.91657 |
Data:Countries/Canada | Quebec | Candiac | 45.38338 |
Data:Countries/Canada | Quebec | Cantley | 45.5668 |
Data:Countries/Canada | Quebec | Cap-Chat | 49.10009 |
Data:Countries/Canada | Quebec | Cap-Santé | 46.67159 |
Data:Countries/Canada | Quebec | Capitale-Nationale | 47.376 |
Data:Countries/Canada | Quebec | Carignan | 45.45008 |
Data:Countries/Canada | Quebec | Carleton | 48.09838 |
Data:Countries/Canada | Quebec | Carleton-sur-Mer | 48.10749 |
Data:Countries/Canada | Quebec | Centre-du-Québec | 46.01985 |
Data:Countries/Canada | Quebec | Chambly | 45.45008 |
Data:Countries/Canada | Quebec | Chambord | 48.43339 |
Data:Countries/Canada | Quebec | Chandler | 48.34935 |
Data:Countries/Canada | Quebec | Chapais | 49.78344 |
Data:Countries/Canada | Quebec | Charlemagne | 45.71678 |
Data:Countries/Canada | Quebec | Château-Richer | 46.96031 |
Data:Countries/Canada | Quebec | Châteauguay | 45.38338 |