1

I have the following three tables Location, Country and CountryAlias. I want to find the locations country. Means I want to search location_name from location table against Country and CountryAlias and find the country code. But while do like query. I am getting wrong output.

location

location_name

Bangalore, India
India
Chennai, India

Country 

code   name
IN     India
IO     British Indian Ocean Territory

CountryAlias

code   alias
IN     Bharth
IN     Hindustan

Try Query

SELECT code from Country 
LEFT JOIN CountryAlias ON Country.code = CountryAlias.code 
where Country.`name` LIKE '%Bangalore, India%' or CountryAlias.alias LIKE '%Bangalore, India%'

O/P = NuLL

 SELECT code from Country 
    LEFT JOIN CountryAlias ON Country.code = CountryAlias.code 
    where Country.`name` LIKE '%India%' or CountryAlias.alias LIKE '%India%'

O/P = IN and IO

Both wrong, Is there any perfect solution. I am using Innodb engine for tables.

Gowri
  • 16,587
  • 26
  • 100
  • 160
  • I don't see the queries using the `Location` table, so how do you expect to find Bangalore? About the last query, it shows what you ask for. I guess you didn't actually want to use `LEFT` join. – ypercubeᵀᴹ Jul 12 '13 at 10:13
  • @ypercube: I taking that location_name from the that location table and giving input to that query. If possible any join solusion. I like hear that too !. But I don't see any matching column – Gowri Jul 12 '13 at 10:19
  • Does the location table have only one column: location_name? And how are you giving the location_name as input to the query? Is it through the LIKE clause? – Stan the DBA Jul 12 '13 at 15:07
  • location table has some other fields but those are not relevant. input is from another query. That is from the location_name column – Gowri Jul 13 '13 at 19:43

0 Answers0