Join to primary table?

letsforum · October 17, 2016, 2:21pm

Here is my query:


SELECT DISTINCT countryinfo.iso_alpha2, countryinfo.name, geoname.name, geoname.asciiname, geoname.alternatenames, geoname.`feature code` , geoname.`country code` , geoname.`admin1 code` , geoname.`admin2 code` , geoname.population, admin1codes.adm1code, admin1codes.adm1name, admin2codes.adm2code, admin2codes.adm2name
FROM `geoname`
LEFT JOIN `countryinfo` ON countryinfo.iso_alpha2 = geoname.`country code`
LEFT JOIN `admin1codes` ON admin1codes.adm1code = CONCAT( geoname.`country code` , '.', geoname.`admin1 code` )
LEFT JOIN `admin2codes` ON admin2codes.adm2code = CONCAT( geoname.`country code` , '.', geoname.`admin1 code` , '.', geoname.`admin2 code` )

Can I join to table geoname and return all results that match this:

CONCAT( geoname.country code , '.', geoname.admin1 code )

and

CONCAT( geoname.country code , '.', geoname.admin1 code , '.', geoname.admin2 code )

WebMachine · October 17, 2016, 2:22pm

I’m moving this question to the databases forum to see what the experts there can do for you.

letsforum · October 17, 2016, 2:23pm

OK sorry

letsforum · October 17, 2016, 2:28pm

I am trying to match es.51.h with this: LEFT JOIN admin2codes ON admin2codes.adm2code = 'geoname.countrycode . geoname.countrycode . geoname.admin2code but ain’t working please help?

I don’t know how to add a point in between the values?

WebMachine · October 17, 2016, 12:39pm

Did you try 'geoname.country_code' . '.' . 'geoname.country' . '.' . 'geoname.admin2' ?

letsforum · October 17, 2016, 12:40pm

Thanks buddy but I error:

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '.' .geoname.`country code`. '.' .geoname.`admin2 code`'
WHERE geoname.asciiname' at line 7

letsforum · October 17, 2016, 12:44pm

LEFT JOIN admin2codes ON admin2codes.adm2code = ‘geoname.country code’.‘.’.‘geoname.country code’.‘.’.‘geoname.admin2 code’

This doesn’t work:

LEFT JOIN admin2codes ON admin2codes.adm2code = 'geoname.country code' '.' 'geoname.country code' '.' 'geoname.admin2 code'

WebMachine · October 17, 2016, 12:58pm

Is it country code or country_code? And in the second statement you left out all the . used for concatenation. Was that a copy and paste error in the post, or did you forget them when you tested the statement?

letsforum · October 17, 2016, 1:00pm

It’s like this:


LEFT JOIN `admin2codes` ON admin2codes.adm2code = 'geoname.`country code`'.'geoname.`country code`'.'geoname.`admin2 code`'

It works when I do this:

LEFT JOIN admin2codes ON admin2codes.adm2code = ‘es.51.h’


Why not above?

WebMachine · October 17, 2016, 1:04pm

That’s not what I originally had - you are missing the . between each part. To do this you need 'item1' . '.' . 'item2' . '.' . 'item3'

letsforum · October 17, 2016, 1:07pm

LEFT JOIN `admin2codes` ON admin2codes.adm2code = 
'geoname.`country code`' . '.' . 'geoname.`country code`' . '.' . 'geoname.`admin2 code`'

You mean this?

It thoughts an error!

letsforum · October 17, 2016, 1:22pm

Here is my entire query:

SELECT DISTINCT countryinfo.iso_alpha2, countryinfo.name, geoname.name, geoname.asciiname, geoname.alternatenames, geoname.`feature code` , geoname.`country code` , geoname.`admin1 code` , geoname.`admin2 code` , geoname.population, admin1codes.adm1code, admin1codes.adm1name, admin2codes.adm2code, admin2codes.adm2name
FROM `geoname`
LEFT JOIN `countryinfo` ON countryinfo.iso_alpha2 = geoname.`country code`
LEFT JOIN `admin1codes` ON admin1codes.adm1code = geoname.`admin1 code`



LEFT JOIN `admin2codes` ON admin2codes.adm2code = 'geoname.`country code`'  '.'  'geoname.`admin1 code`'  '.'  'geoname.`admin2 code`'

WHERE geoname.asciiname REGEXP '[[:<:]]beas[[:>:]]'
LIMIT 0 , 30

TechnoBear · October 17, 2016, 2:29pm

Threads merged.

DaveMaxwell · October 17, 2016, 2:41pm

IF I understand what you’re asking, you could do it this way

LEFT JOIN `admin2codes` ON admin2codes.adm2code = geoname.`country code` + '.' + geoname.`admin1 code` + '.' + geoname.`admin2 code`

Though this would probably be a better way

LEFT JOIN `admin2codes` ON admin2codes.adm2code = CONCAT_WS('.',geoname.`country code`, geoname.`admin1 code`, geoname.`admin2 code`)

letsforum · October 17, 2016, 2:44pm

Thanks buddy that part is solved. Could you take a look at this case this thread has been marged: Join to primary table?

DaveMaxwell · October 17, 2016, 3:02pm

Discourse doesn’t merge threads well. Can you please restate the question, and possibly provide data examples

letsforum · October 17, 2016, 3:08pm

I have this query:

SELECT DISTINCT countryinfo.iso_alpha2, countryinfo.name, geoname.name, geoname.asciiname, geoname.alternatenames, geoname.`feature code` , geoname.`country code` , geoname.`admin1 code` , geoname.`admin2 code` , geoname.population, admin1codes.adm1code, admin1codes.adm1name, admin2codes.adm2code, admin2codes.adm2name
FROM `geoname`
LEFT JOIN `countryinfo` ON countryinfo.iso_alpha2 = geoname.`country code`
LEFT JOIN `admin1codes` ON admin1codes.adm1code = CONCAT( geoname.`country code` , '.', geoname.`admin1 code` )
LEFT JOIN `admin2codes` ON admin2codes.adm2code = CONCAT( geoname.`country code` , '.', geoname.`admin1 code` , '.', geoname.`admin2 code` )

It returns rows that match search query and add to those queries state and province.

Now how can I grab data from this query like country, admin1 and admin2 and re-submit it to same table and return all towns that have this data?

Can it be done with joining or I need a sub-query?

DaveMaxwell · October 17, 2016, 3:52pm

I’m sorry. It’s not sinking in. Can you please provide some sample data and sample output you’re looking for…

r937 · October 17, 2016, 4:19pm

what does “re-submit it to same table” mean???

letsforum · October 18, 2016, 12:47pm

Guys tanks for your replies! Let me explain.

I have a geonames database!

Can I do this?

Check the database for a value let say “valverde del camino” which is a spanish town!

Now lets say a match has been found 1 row!

Can I take data from that row like country code, admin1 and admin2 codes and find return all rows that match this data?

Here is a code!

$sql = "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`
  
  
  WHERE 
  
geoname.asciiname = 'Provincia de Huelva'

";

This returns this:

Array
(
    [0] => Array
        (
            [country] => España
            [feature code] => ADM2
            [country code] => ES
            [admin1code] => 51
            [admin2code] => H
            [town_province] => Provincia de Huelva
        )

)

Now I need a second query to fetch all rows that have:

[country code] => ES
            [admin1code] => 51
            [admin2code] => H

Can I do this with one query?