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
