For anyone who might be interested, here's what finally did the trick and 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).
Thanks again!
Dan
At 03:51 AM 10/15/03, Diana Soares wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
