I believe I tried that -- but when I did (and I just repeated it with the
same result) mysql effectively hangs (i.e. the query takes interminably
long -- I let it run for 20 minutes before killing it).
So should I be looking at an indexing issue? Right now the only things
indexed in the tables are their PKs -- their IDs. city has about 30K
records; zip has about 40K.
Thanks!!
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
