Thanks! I was able to work on the query today, and manually assemble a query
that did the same thing. And thus, was a lot faster. The main contention
MySQL seems to have is the "nested" joins, such as when Access does this:
FROM (((leg_version_hist RIGHT JOIN (leg_comm_hist RIGHT JOIN
leg_activity ON leg_comm_hist.leg_activity_id = leg_activity.id)...
For some reason MySQL doesn't like that. :) It's expecting a table name, not
another JOIN. At least that what it seems to me, but I could be wrong, not
being very experienced with joins. My query in MySQL ended being a lot clean
looking, at least to me:
SELECT leg_activity.activity_date, leg_transitions.trans_desc,
leg_text.leg_version, body_list.body_name
FROM leg_activity LEFT JOIN leg_comm_hist ON leg_activity.id =
leg_comm_hist.leg_activity_id
LEFT JOIN body_list ON leg_comm_hist.body_list_id = body_list.id
LEFT JOIN leg_version_hist ON leg_activity.id =
leg_version_hist.leg_activity_id
LEFT JOIN leg_text ON leg_version_hist.leg_text_id = leg_text.id
LEFT JOIN leg_transitions ON leg_activity.transition_id = leg_transitions.id
WHERE leg_activity.leg_header_id = 8
And it works very well. I'm happy. :) Thanks for the pointers!
j----- k-----
On Tuesday 26 June 2001 17:57, Rolf Hopkins wrote:
> As it is such a big query, I don't have time to look at it for you but it
> may be easier if you convert the RIGHT JOINS to LEFT JOINS for starters.
> The manual does recommend LEFT JOINS, mainly for portability.
>
> First part would be something like
>
> (leg_activity LEFT JOIN leg_comm_hist ON (leg_comm_hist.leg_activity_id =
> leg_activity.id) LEFT JOIN leg_version_hist ON
> leg_version_hist.leg_activity_id = leg_activity.id)
>
> ----- Original Message -----
> From: "Joshua J. Kugler" <[EMAIL PROTECTED]>
> To: "MySQL" <[EMAIL PROTECTED]>
> Sent: Wednesday, June 27, 2001 2:47
> Subject: Conversion from Access JOIN syntax to MySQL JOIN syntax...
>
> > Yes, I've read the docs on JOIN syntax, and search the list archives for
> > similar information.
> >
> > I have this query:
> >
> > SELECT leg_activity.activity_date, leg_transitions.trans_desc,
> > leg_text.leg_version, body_list.body_name
> > FROM (((leg_version_hist RIGHT JOIN (leg_comm_hist RIGHT JOIN
> > leg_activity
>
> ON
>
> > leg_comm_hist.leg_activity_id = leg_activity.id)
> > ON leg_version_hist.leg_activity_id = leg_activity.id) LEFT JOIN leg_text
>
> ON
>
> > leg_version_hist.leg_text_id = leg_text.id)
> > INNER JOIN leg_transitions ON leg_activity.transition_id =
> > leg_transitions.id) LEFT JOIN body_list ON leg_comm_hist.body_list_id =
> > body_list.id
> > WHERE leg_activity.leg_header_id = 8
> >
> > That was generated by Access's query designer. It works under Access,
> > but passing to MySQL through ODBC, Access generates four or five queries
> > to do the job. This make it a very long query (1 or 2 seconds), not
> > appropriate for browsing through records.
> >
> > Trying to give this directly to MySQL generates an error:
> >
> > You have an error in your SQL syntax near '(leg_comm_hist RIGHT JOIN
> > leg_activity ON leg_comm_hist.leg_ac' at line 2.
> >
> > I understand JOIN's, at least in concept, but not well enough to
> > construct manually. Is there a tool out there similar to Access's query
> > designer
>
> that
>
> > will design MySQL compatible queries? Are there rules for convert Access
> > queries to MySQL that I should be aware of?
> >
> > Thanks for any help you can give.
> >
> > --
> > Joshua Kugler, Information Services Director
> > Associated Students of the University of Alaska Fairbanks
> > [EMAIL PROTECTED], 907-474-7601
> >
> > ---------------------------------------------------------------------
> > 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
>
> ---------------------------------------------------------------------
> 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
--
Joshua Kugler, Information Services Director
Associated Students of the University of Alaska Fairbanks
[EMAIL PROTECTED], 907-474-7601
---------------------------------------------------------------------
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
