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
