Join to primary table?

Thank you. That’s what we needed. An example with some data to help us understand what you were looking for.

You can if you use a subquery. Use the subquery to find the country, admin1 and admin2 codes for the location, then join the query to find all the combinations that match

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`
		 LEFT JOIN `countryinfo` ON countryinfo.iso_alpha2 = geoname.`country code`
		WHERE geoname.asciiname = 'Provincia de Huelva') SQ ON SQ.`country code` = geoname.`country code` AND
								       SQ.`admin1 code` = geoname.`admin1 code` AND 
								       SQ.`admin2 code` = geoname.`admin2 code`
1 Like

sure

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 FROM geoname WHERE asciiname = 'Provincia de Huelva' ) AS this INNER JOIN geoname ON geoname.`country code` = this.ctry AND geoname.`admin1 code` = this.admin1 AND geoname.`admin2 code` = this.admin2 INNER JOIN countryinfo ON countryinfo.iso_alpha2 = geoname.`country code`

p.s. notice you don’t need DISTINCT

1 Like

OMG is that awesome or what works amazing. I would never come up with this. Buddy I love you you just saved the day +1 and +1 1milllion thanks.

One more question please?

The code you posted works for towns and province! However state and country doesn’t have admin2 code!

Is there a way to use admin2 code! when it’s available and not when it’s doesn’t?

I mean if result is ADM1 output all it’s provinces ADM2’s and if it’s PCLI output it’s states ADM1’s.

State

Array
(
    [0] => Array
        (
            [country] => España
            [feature code] => ADM1
            [country code] => ES
            [admin1code] => 51
        )

)

Country

Array
(
    [0] => Array
        (
            [country] => España
            [feature code] => PCLI
            [country code] => ES
        )

)

by “not available” do you mean NULL?

Some like this that works when it finds a state it will output it’s ADM2:

If primary result PCLI find it’s ADM1
If primary result ADM1 find it’s ADM2
If primary result ADM2 find it’s country code, admin1 and admin2

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
FROM geoname
WHERE asciiname = 'andalucia'
) 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.`admin1 code` = this.admin12
AND geoname.`feature code` = 'ADM2'
INNER
JOIN countryinfo ON countryinfo.iso_alpha2 = geoname.`country code`

OK, that makes things much more interesting (knowing all the variables helps to solve the problem)

Basically, what you’ll have to do is just join the sub query by country code, then use the where clause to select which rows actually match up. I THINK this query (my MySQL is a little rusty so the syntax might be off) will work, but you’ll want to test it thoroughly…

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

DISTINCT not necessary, Dave

also, what’s wrong with his OR solution?

Buddy you got the point thanks for that. It returns 0 results please give it one more shot!

I know. I just forgot to take it out.

Probably nothing…but it wasn’t there when I switched over to my text editor (I’m not a fan of formatting text in discourse) to change the query. He’d have to test it out to ensure it meets his criteria - though his idea doesn’t seem to support what his data examples had :stuck_out_tongue_winking_eye:

Not sure why returns 0 results!

Buddy you are so close I can almost smell the victory please try to make it work!

OK, try this. Take that whole case statement and copy it into the select statement, then take it out of the where clause.

My guess is it’s returning 0 all the time. Which may mean some criteria you’re giving me isn’t consistent (for example, your SQL statement doesn’t match the sample output you provided)

This returns data:

SELECT 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.`country code` , geoname.`admin1 code` , geoname.`admin2 code` 
FROM  `geoname` 
WHERE geoname.asciiname =  'espana')SQ ON SQ.`country code`  = geoname.`country code` 
LIMIT 0 , 30

Right, and I expected it would since it’s returning everything but the join. You didn’t add the case statement into the select portion. Can you add it onto the select statement and show me what it returns?

You mean add this:

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

yes, except remove the WHERE and the > 1 or it’ll give you an error

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`
(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)

Like this? Case it says: #1305 - FUNCTION geoname.country code does not exist

No, you have to move it into the SELECT portion

SELECT DISTINCT countryinfo.iso_alpha2
     , countryinfo.name
     , geoname.asciiname
     , geoname.`feature code`
     , geoname.`country code`
     , geoname.`admin1 code`
     , geoname.`admin2 code`
     , (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) CheckValue
 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`

It returns all the data for country spain!