Dan Hansen wrote:
> is essentially giving me what I need:
>
> CREATE TEMPORARY TABLE temptable
> SELECT state.name AS state , group.name AS group,
> group.zip AS zip, city.name AS city
> FROM city, group, zip
> LEFT JOIN state ON city.state_id = state.id
> WHERE group.zip = zip.zip
> AND zip.city_id = city.id;
>
> INSERT INTO temptable
> SELECT name, NULL, -1, NULL FROM state;
>
> SELECT state, group, zip, city
> FROM temptable
> ORDER BY state, zip, group ;
>
> (Once plugged into PHP code, I will replace "temptable" with a unique
> code-generated string).
>
Should run in a single query too...
SELECT state.name AS state , group.name AS group,
group.zip AS zip, city.name AS city
FROM city, group, zip
LEFT JOIN state ON city.state_id = state.id
WHERE group.zip = zip.zip
AND zip.city_id = city.id
UNION
SELECT name, NULL, -1, NULL FROM state
ORDER BY state, zip, group ;
.. although untested. -do remove the semicolon twice-
Hans
