"DL Neil" <[EMAIL PROTECTED]> on 12/06/2001 12:26:45 AM
Internet mail from:
Please respond to "DL Neil" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
cc: <[EMAIL PROTECTED]>
Subject: Re: select statement group by unusual results
Chip,
> > I have a database with 12 tables. I am running a query to select
certain
> > fields
> > from 3 of the tables, like this -
> >
> > select Title, Details, StartDate, City, State
> > from phpCalendar_Details, phpCalendar_Daily, phpCalendar_EventLocations
> > where phpCalendar_Details.EventLocationID =
> phpCalendar_EventLocations.EventLocationID
> > group by City;
> >
> > The problem is the StartDate field data for the first returned row is
> carried down through
> > all the rows, rather than picking up the correct StartDate data. All
the
> other data is
> > picked up correctly. When I change group by to State or Title or any
> field, I get the same
> > problem, whatever the first returned field, it is carried down through
> all the rows. How do I
> > get around this, or what am I doing wrong?
>
> |From this can we assume (you don't give table schema) that StartDate is
in
> the |phpCalendar_Daily table and is
> |the only field selected from that table?
> |
> |Take a look at the WHERE clause: the query is using three tables but
only
> joins two.
> |
> |Is that it?
> |=dn
>
> I guess I left a bit out. Here's the set up -
> 3 tables are phpCalendar_Details (fields are Title, Details),
> phpCalendar_Daily (field StartDate) , phpCalendar_EventLocations (fields
> are City, State). Two tables (_Details and _EventLocations)
> have a common field - EventLocationID, the third table does not have a
> field common to those two tables. I suppose I have to have one common to
> all 3 to make it work?
|=you got it - that's why they're called "relational" databases!
|-otherwise there is no way of working out which row/StartDate from the
phpCalendar_Daily |table is relevant to
|the Event being described be the other two tables' data.
|
|=Should you be thinking about having an EventID which appears in both the
|phpCalendar_Details and
|phpCalendar_Daily tables - or collapsing the StartDate into the
phpCalendar_Details table? |Either way, it is
|beginning to sound like a db design/normalisation issue rather than a
query-writing |problem.
|
|=Hope it helps,
|=dn
Thanks for the tips. So here's my latest attemp:
select distinct Title, Details, StartDate, StopDate, City, State
from phpCalendar_Details t1, phpCalendar_Daily t2, phpCalendar_EventLocations t3
where t1.LocationID = t2.LocationID
and t1.EventLocationID = t3.EventLocationID
group by StartDate;
And this one works! Amazing, my first attempt at a join statement.
Thanks again,
Chip W.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
