sql query - select ...like

archVille · #1 (**permalink**) 11-17-05, 13:00

Does anyone knows how to build a simple select query from 2 tables :
airports(code,airp_name) and countries(count_name,x,y,z,...)

Notice the Airport name is for example a simple string containg the name of the country : "Airport of Florida,USA"
select code,count_name from airports,countries from airports,countries
where airp_name like ?-?-?-?-?
so to search the string airp_name and find the specific country? (USA fro the above example).

I have tried some with like '% %' but nothing works !!!
I want in the ?-?-?-? to put somehow to search the airp_name to find the country inside of it.

Any idea????

Littlefoot · #2 (**permalink**) 11-18-05, 02:51

Regarding the fact that I "speak" Oracle SQL (don't know which DB you use), this could be discussed this way:

I'd say that LIKE operator won't help much in this situation - it would be OK if you wanted to select all USA airports, such as

SELECT a.code, a.airp_name
FROM airports a
WHERE a.airp_name like '%&airport_name%';

However, to be able to select data you need, there's one assumption: values stored in "airports.airp_name" must end with ", country_name" (comma - country_name). If all of them follow the same rule, query might look like this:

Code:

SELECT a.code, c.count_name
  FROM airports a, countries c
 WHERE c.count_name =
          LTRIM (RTRIM (SUBSTR (a.airp_name,
                                INSTR (a.airp_name, ',', -1) + 1,
                                LENGTH (a.airp_name)
                               )
                       )
                );

Basically, "countries.count_name" must be equal to country name which is part of "airports.airp_name" column; listed functions are used to extract country name from the airport name.

Finally, I'd say that such a design isn't the best one - country name shouldn't be stored together with airport name (in the same column, I mean).

I hope you'll be able to use some of this and solve the problem.

archVille · #3 (**permalink**) 11-18-05, 04:45

Quote:

Originally Posted by Littlefoot

Regarding the fact that I "speak" Oracle SQL (don't know which DB you use), this could be discussed this way:

I'd say that LIKE operator won't help much in this situation - it would be OK if you wanted to select all USA airports, such as

SELECT a.code, a.airp_name
FROM airports a
WHERE a.airp_name like '%&airport_name%';

However, to be able to select data you need, there's one assumption: values stored in "airports.airp_name" must end with ", country_name" (comma - country_name). If all of them follow the same rule, query might look like this:

Code:

SELECT a.code, c.count_name
  FROM airports a, countries c
 WHERE c.count_name =
          LTRIM (RTRIM (SUBSTR (a.airp_name,
                                INSTR (a.airp_name, ',', -1) + 1,
                                LENGTH (a.airp_name)
                               )
                       )
                );

Basically, "countries.count_name" must be equal to country name which is part of "airports.airp_name" column; listed functions are used to extract country name from the airport name.

Finally, I'd say that such a design isn't the best one - country name shouldn't be stored together with airport name (in the same column, I mean).

I hope you'll be able to use some of this and solve the problem.

Hm... I have tried the 1st you are suggesting.The problem i guess in Mysql is that when i put "where a.airp_name like '%&country%' it takes the string &country and tried to find if there is a &country in the airp_name and not the name of the country,if you know what i mean.This was the basic problem!
I dont know how to put the country inside '% %'.
If i try to put something else it looks out only for the specific string inside airp_name and not what i want.(It looks only for "&country" or "country" and not USA,Portugal,Spain,Greece,.... "

This is the problem i cant solve in MySql.Any IDEA??
As for the 2nd its abit difficult to seperate the string because names,places and countries in string are mixed and not with a standard order!

Littlefoot · #4 (**permalink**) 11-18-05, 05:20

"&" sign, in Oracle, shows SQL engine to ask user for input. This would produce "Enter value for country: " prompt. I don't know a similar way in MySQL.

As for the second approach, well, I told you that such a design isn't good ... I really wouldn't know how to find out the country name. Perhapy by using comparation between every word in "airport.airp_name" with existing values (country names) in "countries" table? To do so, you'll probably need some kind of procedural extension to SQL.

However, perhaps someone will know a better solution.

healdem · #5 (**permalink**) 11-18-05, 05:33

I've never had a requirement to use them, but MySQL has a rich source of string functions such as instr, find_in, length etc... All of these can be used in a select query. I'd suggest a bit of "RTFM" may provie valuable.

archVille · #6 (**permalink**) 11-18-05, 05:34

Quote:

Originally Posted by Littlefoot

"&" sign, in Oracle, shows SQL engine to ask user for input. This would produce "Enter value for country: " prompt. I don't know a similar way in MySQL.

As for the second approach, well, I told you that such a design isn't good ... I really wouldn't know how to find out the country name. Perhapy by using comparation between every word in "airport.airp_name" with existing values (country names) in "countries" table? To do so, you'll probably need some kind of procedural extension to SQL.

However, perhaps someone will know a better solution.

I am not very familiar with Oracle but i guess that is working on Oracle environment.The problem is in Mysql when you try to put anything in '% %' it takes only the given string as a string and i cant enter there a function to take the value of "country" to take out all the countries "USA,Greece,...".

(still eeking...)

Internet Services

Web Development

Digital Marketing

Consumer Tech