You're confusing the left/right "sides" of LEFT JOIN...
Using LEFT JOIN, it is the right table that is dependent on the left
table. All results from left table are selected.
So you may try:
SELECT state.name AS state , group.name AS group,
group.zip AS zip, city.name as city
FROM state
LEFT JOIN city ON city.state_id = state.id
LEFT JOIN zip ON zip.city_id = city.id
LEFT JOIN group ON group.zip = zip.zip
Hope this helps,
--
Diana Soares
On Tue, 2003-10-14 at 22:27, D. R. Hansen wrote:
> 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
