Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Posts
    40

    Unanswered: sql query - select ...like

    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????

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  3. #3
    Join Date
    Nov 2005
    Posts
    40
    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!


  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "&" 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.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.

  6. #6
    Join Date
    Nov 2005
    Posts
    40
    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...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •