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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
