Double inner joins/ nested inner joins?
Hi out there! We are converting our old access database to MySql, about time! we are experiencing some problems with double nested joins, don't know what else to call them. the query in access looks something like this: SELECT arrangement.Navn, arrangement.StartDato, arrangement.SlutDato, arrangement.vises, arrangement.ArrId, enhed.Navn, arrangement.møde FROM enhed INNER JOIN (arrangement INNER JOIN [Arr/enhed] ON arrangement.ArrId = [Arr/enhed].ArrId) ON enhed.EnhedId = [Arr/enhed].EnhedId WHERE (((arrangement.StartDato)=01-01-2001 And (arrangement.StartDato)=02-02-2002) AND ((arrangement.vises)=Yes) AND ((arrangement.møde)=No)); by fixing it up a litle, it should work in MySql: SELECT arrangement.Navn, arrangement.StartDato, arrangement.SlutDato, arrangement.vises, arrangement.ArrId, enhed.Navn, arrangement.møde FROM enhed INNER JOIN (arrangement INNER JOIN Arr_enhed ON arrangement.ArrId = Arr_enhed.ArrId) ON enhed.EnhedId = Arr_enhed.EnhedId WHERE (((arrangement.StartDato)=01-01-2001 And (arrangement.StartDato)=02-02-2002) AND ((arrangement.vises)=1) AND ((arrangement.møde)=0)); The thing is, it does'nt and no errors are returned. the question beeing: Can we use that kind of join combination: table_ref INNER JOIN (table_ref INNER JOIN table_ref ON join_cond) ON join_cond if no, how do we work arround it? Mads Martin - 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
RE: Nested Inner Joins
I am having similar nested join problems with the following: SELECT tblThread.Message_date, tblAuthor.Username, tblAuthor.Author_ID, tblTopic.Forum_ID FROM (tblForum INNER JOIN tblTopic ON tblForum.Forum_ID = tblTopic.Forum_ID) INNER JOIN (tblAuthor INNER JOIN tblThread ON tblAuthor.Author_ID = tblThread.Author_ID) ON tblTopic.Topic_ID = tblThread.Topic_ID ORDER BY tblThread.Message_date DESC I looked at Bob Hall's posting back in Dec, but being new to SQL can't quite get my head around it. Could anybody be a real champ and translate the sql code above from an MS ACCESS based site in to MySQL? Many thanks Regards Martin Schmalenbach [EMAIL PROTECTED] - 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
Re: Nested Inner Joins
On Tue, Dec 11, 2001 at 07:34:11PM -0800, Kendra L Knudtzon wrote: I am having problems with this nested inner join statement: SELECT tblMethodType.MethodType, tblMethodParm.MethodName, tblParm.ParmName, tblParm.Label, tblParm.Value, tblParm.Unit, tblParm.Tip FROM tblMethodType INNER JOIN (tblMethod INNER JOIN (tblParm INNER JOIN tblMethodParm ON tblParm.idParm = tblMethodParm.idParm) ON tblMethod.MethodName = tblMethodParm.MethodName) ON tblMethodType.MethodType = tblMethod.MethodType; I have not had much experience with INNER JOINS, and I read one website that said the MySql can't support grouped inner joins... If this is true, how do I get this type of command to execute under MySql (I inherited this code but the statement apparently worked with Microsoft Access) MySQL supports grouped INNER JOINs just fine. But you can't put an inner INNER JOIN between the outer INNER JOIN and the ON keyword. Try FROM (inner INNER JOIN clause) INNER JOIN tblMethodType ON ... Do the same thing with the innermost INNER JOIN clause. What you typed above works fine in Access because Access doesn't care about the order. Bob Hall -- Kanskje, kanskje en gang - sier du til meg - database, table, query RolfJacobSQL - 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
Nested Inner Joins
I am having problems with this nested inner join statement: SELECT tblMethodType.MethodType, tblMethodParm.MethodName, tblParm.ParmName, tblParm.Label, tblParm.Value, tblParm.Unit, tblParm.Tip FROM tblMethodType INNER JOIN (tblMethod INNER JOIN (tblParm INNER JOIN tblMethodParm ON tblParm.idParm = tblMethodParm.idParm) ON tblMethod.MethodName = tblMethodParm.MethodName) ON tblMethodType.MethodType = tblMethod.MethodType; I have not had much experience with INNER JOINS, and I read one website that said the MySql can't support grouped inner joins... If this is true, how do I get this type of command to execute under MySql (I inherited this code but the statement apparently worked with Microsoft Access) You may respond directly to me, if possible. Thank you. Kendra Knudtzon - 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
