Re: MySQL Multi-Table Select Query Problem
| From: | Hugh Bothwell | Date: | Wed, 15 Aug 2001 13:17:45 +0000 |
| Subject: | Re: MySQL Multi-Table Select Query Problem | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-62817@lists.php.net to get a copy of this message | ||
"Charles Williams" <hosting.mailing.list.account@acnshosting.com> wrote in
message news:008801c12588$80bc7960$fe0aa8c0@chucks...
> Hey folks,
>
> if you go to http://www.acnsnet.com/czc/show.php?state=Bayern
> You can see
> my problem with this query. This thing is killin me so if you have any
> ideas just shout.
This looks like a case of poor database design...
If you had a 'cities' table and referred to it from your
'restaurants', 'hotels', and 'clubs' tables, the query
would be obvious. (It would slightly complicate
the logic for adding restaurants, hotels, and clubs -
but how often do you do that, compared to
querying the database?)
While you're doing that, I would consider having
a 'states' table too - reduce the number of
text comparisons...
Luckily, a stop-gap is available... create and
populate a temporary cities table, and get your
data from that.
CREATE TEMPORARY TABLE cities
SELECT city FROM restaurant
WHERE state='Bayern';
INSERT INTO cities
SELECT city FROM hotels
WHERE state='Bayern';
INSERT INTO cities
SELECT city FROM clubs
WHERE state='Bayern';
SELECT DISTINCT city FROM cities ORDER BY city;
... how's that?