Re: difficult sql statement. Help needed
| From: | (Lutz Brückner) | Date: | Wed, 10 Apr 2002 17:42:28 +0000 |
| Subject: | Re: difficult sql statement. Help needed | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-18403@lists.php.net to get a copy of this message | ||
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