Hi Andy,

> There are 3 tables: provinces, cities, countries
> The result should be city, province, country but only if province is
> available. If not just display city and country.!
> 
> Lets say we are searching for madrid.
> 
> I did build following statement:
>      SELECT c.city, c.ID, p.province, co.country FROM cities c, provinces p,
> countries co WHERE c.city like 'madrid%' AND p.province_id = c.province_id
> AND p.country_code = c.country_code AND c.country_code = co.country_code
> ORDER BY country , province limit 0, 10

thats really easy if you know how to use the join-syntax:

SELECT c.city, c.ID, p.province, co.country 
  FROM cities AS c
 INNER JOIN countries AS co ON c.country_code=co.country_code 
  LEFT JOIN provinces AS p  ON c.province_id=p.province_id
 WHERE c.city like 'madrid%' 
 ORDER BY country , province limit 0, 10

Ciao,
        Lutz

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to