Uberdumb question - but I'm still enough of a newbie that this is giving
me fits...
I have four tables, with relevant columns as follows:
++++++++++++++ ++++++++++++++
group zip
-------------- --------------
name varchar city_id int
zip mediumint zip mediumint
++++++++++++++ ++++++++++++++
state city
-------------- --------------
id int id int
name varchar name varchar
state_id int
[group]<-n..1->[zip]<-n..1->[city]<-n..1->[state]
I want my query to return a list that includes all states -- regardless of
whether it matches any records in the other tables. The queries below (and
I have tried many other permutations without
success) returns only rows for states where there is a corresponding group
record:
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
SELECT state.name AS state , group.name AS group,
group.zip AS zip, city.name as city
FROM group
LEFT JOIN zip ON zip.zip = group.zip
LEFT JOIN city ON city.id = zip.city_id
LEFT JOIN state ON state.id = city.state_id
My test data returns the following data (yes, all other tables are fully
populated).
+----------+-------------+-------+------------------+
| state | group | zip | city |
+----------+-------------+-------+------------------+
| Illinois | Test Group | 60070 | Prospect Heights |
+----------+-------------+-------+------------------+
1 row in set (0.41 sec)
I'd like to see
+----------+-------------+-------+------------------+
| state | group | zip | city |
+----------+-------------+-------+------------------+
.
.
.
| Idaho | NULL | NULL | NULL |
| Illinois | Test Group | 60070 | Prospect Heights |
| Indiana | NULL | NULL | NULL |
.
.
.
etc...
Can anyone tell me where I'm blowing it?
Dan Hansen
