For everyone who helped, THANK YOU!!
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/mysql?unsub=1
