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
