Join to primary table?

OK, what does it return for CheckValue? 0 or 1?

I see 1

Provincia de Huelva is ADM2 has to return rows that have ES(country code) 51(admin1 code) H(admin2 code)

That doesn’t make any sense…the original query should have worked then…Ugh…I’m an idiot. In the original query I gave you, change the > 1 to = 1

Oopsie…:blush:

SELECT DISTINCT countryinfo.iso_alpha2
     , countryinfo.name
     , geoname.asciiname
     , geoname.`feature code`
     , geoname.`country code`
     , geoname.`admin1 code`
     , geoname.`admin2 code`
 FROM `geoname`
 LEFT JOIN `countryinfo` ON countryinfo.iso_alpha2 = geoname.`country code`
 JOIN (SELECT DISTINCT geoname.`country code`
		     , geoname.`admin1 code`
		     , geoname.`admin2 code`
		 FROM `geoname`
		WHERE geoname.asciiname = 'Provincia de Huelva') SQ ON SQ.`country code` = geoname.`country code`
 WHERE (CASE geoname.`feature code`
	     WHEN 'ADM2' AND SQ.`admin1 code` = geoname.`admin1 code` AND SQ.`admin2 code` = geoname.`admin2 code` THEN 1
	     WHEN 'ADM1' AND SQ.`admin1 code` = geoname.`admin1 code` THEN 1
  	     WHEN 'PCLI' THEN 1
	     ELSE 0
	END) = 1

I am so sorry returns data for entire ES country!

Maybe it’s much easier using temp table?

Ohhhh! I think I understand now. The feature code check is based on where you SEARCH for, not for the rest of the records? In other words, if you search for Provincia de Huelva, then it has to be an ADM2 match. If you search for Spain, it’s just a country match?

If that’s the case, then you need to add the feature code to the sub query and change the where clause to use the sub query value.

SELECT DISTINCT countryinfo.iso_alpha2
     , countryinfo.name
     , geoname.asciiname
     , geoname.`feature code`
     , geoname.`country code`
     , geoname.`admin1 code`
     , geoname.`admin2 code`
 FROM `geoname`
 LEFT JOIN `countryinfo` ON countryinfo.iso_alpha2 = geoname.`country code`
 JOIN (SELECT DISTINCT geoname.`feature code`
		     , geoname.`country code`
		     , geoname.`admin1 code`
		     , geoname.`admin2 code`
		 FROM `geoname`
		WHERE geoname.asciiname = 'Provincia de Huelva') SQ ON SQ.`country code` = geoname.`country code`
 WHERE (CASE SQ.`feature code`
	     WHEN 'ADM2' AND SQ.`admin1 code` = geoname.`admin1 code` AND SQ.`admin2 code` = geoname.`admin2 code` THEN 1
	     WHEN 'ADM1' AND SQ.`admin1 code` = geoname.`admin1 code` THEN 1
  	     WHEN 'PCLI' THEN 1
	     ELSE 0
	END) = 1

I edited your query a bit and it seems to work. Please can you optimize it?

SELECT countryinfo.iso_alpha2, countryinfo.name, geoname.asciiname, geoname.`feature code` , geoname.`country code` , geoname.`admin1 code` , geoname.`admin2 code`
FROM (
SELECT `country code` AS ctry, `admin1 code` AS admin1, `admin2 code` AS admin2, `country code` AS ctry2, `admin1 code` AS admin12, `admin2 code` AS admin22
FROM geoname
WHERE asciiname = 'espana'
) AS this
INNER
JOIN geoname ON geoname.`country code` = this.ctry
AND geoname.`admin1 code` = this.admin1
AND geoname.`admin2 code` = this.admin2

OR 

geoname.`country code` = this.ctry2
AND geoname.`feature code` = 'ADM1'

OR

geoname.`country code` = this.ctry2
AND geoname.`admin1 code` = this.admin12
AND geoname.`feature code` = 'ADM2'

INNER
JOIN countryinfo ON countryinfo.iso_alpha2 = geoname.`country code`

Buddy you are amazing spent all this time helping me out not sure how you were able to read my mind and know all this stuff.

It keeps returning all rows.

BUT listen the first solution you posted tweged a bit by me works BUT I am not sure is it can still be optimized to be faster!

I see it shows duplicate rows when it’s a town not ADM1 nor ADM2 nor PCLI!

OK, the problem here is you’re saying one thing and querying for something else. Which is right?

In post 43, you say this:

Provincia de Huelva is ADM2 has to return rows that have ES(country code) 51(admin1 code) H(admin2 code)

But your query is not joining that, it’s only looking at admin1

geoname.`country code` = this.ctry2
AND geoname.`admin1 code` = this.admin12
AND geoname.`feature code` = 'ADM2'

I am not sure what I said before case it difficult to tell you what I mean.

If
PCLI look for country code and admin1 code and feature code = ADM1

ADM1 look for country code and admin1 code and admin2 code and feature code = ADM2

ADM2 look for country code and admin1 code and admin2 code

If non above find all country code and admin1 code and admin2 code.

Sorry to be dense, but I’m still confused.

Where is PCLI, ADM1 and ADM2? On the search term? And the feature code is on the geocode table, not the one on the search term?

Can you please provide some sample data and some examples of each search term and what the expected results would be?

I am sorry!

I mean if you search for España which is a country that has feature code PCLI.

So if a match found check its feature code if PCLI then find all it’s states ADM1 and so on.

http://www.geonames.org/export/codes.html

NOW I get it…I think.

You can use your version, but if you want to try mine, I think (hope) this will work.

SELECT DISTINCT countryinfo.iso_alpha2
     , countryinfo.name
     , geoname.asciiname
     , geoname.`feature code`
     , geoname.`country code`
     , geoname.`admin1 code`
     , geoname.`admin2 code`
 FROM `geoname`
 LEFT JOIN `countryinfo` ON countryinfo.iso_alpha2 = geoname.`country code`
 JOIN (SELECT geoname.`feature code`
	    , geoname.`country code`
	    , geoname.`admin1 code`
	    , geoname.`admin2 code`
	 FROM `geoname`
	WHERE geoname.asciiname = 'Provincia de Huelva') SQ ON SQ.`country code` = geoname.`country code` AND SQ.`admin1 code` = geoname.`admin1 code`   
 WHERE (CASE SQ.`feature code`
             WHEN 'PCLI'  AND  geoname.`feature code` = 'ADM1' THEN 1
	     WHEN 'ADM1' AND SQ.`admin2 code` = geoname.`admin2 code`  AND  geoname.`feature code` = 'ADM2' THEN 1
            ELSE IF(SQ.`admin2 code` = geoname.`admin2 code`, 1, 0)
	END) = 1
1 Like

OH buddy thanks so I mean so much. You are amazing spend all this time figuring this stuff out. Man love you bro.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.