#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    4
    Rep Power
    0

    Out of practice - need a hint to correct direction


    Hi,

    I have taken a looong break from programming. Other things to to.
    Now I have to restart and am stumbling already.

    Problem is:
    Ive got two tables,

    one lists houses and has (amongst others) one field (integer) which holds the key of the city the house is in.

    The second table simply lists cities (key; city_name).

    Now I need to find the names (or keys) of all cities that have houses.

    Would you believe I forgot how to start...
    Please someone gimme a push in the right direction... (need sql query statement)

    Ah- almost forgot, its Mysql and php we're talking.

    Thanks in advance from sunny Tenerife island (Spain)
    Dennis
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,374
    Rep Power
    391
    Code:
    select city_name
      from cities
     where exists
            (select *
                from houses
               where cities.city_key = houses.city_key)
    Change table and column names as apropriate.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    4
    Rep Power
    0
    Thank you very much...

    that put me back on track.

    Greets
    Dennis
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    4
    Rep Power
    0

    More help needed...


    First, thanks again.
    It worked as long as I needed to compare only one attribute.
    Now I need to compare a second (using AND in commented line to see if that object is active) and it doesnt work anymore...

    Because I need it to only show * from orte which has active objects...

    Can you help?

    [PHPNET=""]
    $SQL = "SELECT * orte ";
    $SQL .= "where exists ";
    $SQL .= "(select * from objekte ";
    $SQL .= "where "
    $SQL .= "orte.ort_id = objekte.obj_ort_id) ";
    // $SQL .= " AND objekte.obj_aktiv = 1) ";
    if ($region_id != 0) {
    $SQL .= "AND ort_region_id = ".$region_id." ";
    }
    [/PHPNET]
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    4
    Rep Power
    0
    GOT IT!
    No reply needed.
    ...Bloody brackets...
    Thanks anyway
    Dennis

IMN logo majestic logo threadwatch logo seochat tools logo