Re: multiple Inner Joins
Protoculture wrote: > Although I think we are getting closer with the last bit of code Jochem > posted. > > Error: > Server Msg: -3010, State: 07002, [Microsoft][ODBC Microsoft Access Driver] > Too few parameters. Expected 1. > > SQL: > SELECT * > FROM auction_item_categories_sub5 AS s5 > INNER JOIN auction_item_categories_sub4 AS s4 ON s5.category_id = > s4.id ) > INNER JOIN auction_item_categories_sub3 AS s3 ON s4.category_id = > s3.id ) > INNER JOIN auction_item_categories_sub2 AS s2 ON s3.category_id = > s2.id ) > INNER JOIN auction_item_categories_sub AS s ON s2.category_id= > s.id ) > INNER JOIN auction_item_categories AS c ON s.category_id = > c.id > WHERE auction_item_categories.id = 1 This is not my last code, my last code had: WHERE c.id = 1 Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190632 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: multiple Inner Joins
> SQL: > SELECT * > FROM auction_item_categories_sub5 AS s5 > INNER JOIN auction_item_categories_sub4 AS s4 ON > s5.category_id = s4.id ) > INNER JOIN auction_item_categories_sub3 AS s3 ON > s4.category_id = s3.id ) > INNER JOIN auction_item_categories_sub2 AS s2 ON > s3.category_id = s2.id ) > INNER JOIN auction_item_categories_sub AS s ON > s2.category_id= s.id ) > INNER JOIN auction_item_categories AS c ON > s.category_id = c.id > WHERE auction_item_categories.id = 1 You are sill missing the alias in the SELECT. First off, selecting * is not a good idea at all since my guess is that you don't need all the columns. So, change that SELECT * to something else depening upon which table you need the values from. SELECT s.*, s2.*, s3.*, s4.*, s5.* Get rid of the * above and list the columns you want. Mike ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190631 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
Adam, the code you gave didn't seem to work. same goes with Jochem. Although I think we are getting closer with the last bit of code Jochem posted. Error: Server Msg: -3010, State: 07002, [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. SQL: SELECT * FROM auction_item_categories_sub5 AS s5 INNER JOIN auction_item_categories_sub4 AS s4 ON s5.category_id = s4.id ) INNER JOIN auction_item_categories_sub3 AS s3 ON s4.category_id = s3.id ) INNER JOIN auction_item_categories_sub2 AS s2 ON s3.category_id = s2.id ) INNER JOIN auction_item_categories_sub AS s ON s2.category_id= s.id ) INNER JOIN auction_item_categories AS c ON s.category_id = c.id WHERE auction_item_categories.id = 1 >Adam Howitt wrote: >> I'm not sure why you are using parenthesis for your joins since SQL >> doesn't need them. > >Access ... > > >> This may sound horribly simple but I noticed a >> change in your field naming convention throughout this thread. You have >> category_id for all your tables except for your alias s4 >> (auction_item_categories_sub4) where you have a field with an extra 'y' >> categoryy_id > >So that is one error, and the other one is the missing alias in >the predicate: >SELECT * >FROM auction_item_categories_sub5 s5 >INNER JOIN auction_item_categories_sub4 s4 ON >s5.category_id = s4.id) >INNER JOIN auction_item_categories_sub3 s3 ON >s4.category_id = s3.id) >INNER JOIN auction_item_categories_sub2 s2 ON >s3.category_id = s2.id) >INNER JOIN auction_item_categories_sub s ON >s2.category_id = s.id) >INNER JOIN auction_item_categories c ON s.category_id >= c.id >WHERE c.id = 1 > >Jochem ~| Find out how to get a fax number that sends and receives faxes using your current email address http://www.houseoffusion.com/banners/view.cfm?bannerid=64 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190630 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
Jochem van Dieten wrote: > >>I'm not sure why you are using parenthesis for your joins since SQL >>doesn't need them. > > Access ... I know it's not really relevant to THIS discussion, but some database servers DO require parentheses around multiple joins our DB2 mainframe does. So I tend to use them in all situations. ie... SELECT ... FROM (( table1 inner join table 2 on ...) inner join table 3 on ...) inner join table 4 on ... WHERE ... ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190625 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
Adam Howitt wrote: > I'm not sure why you are using parenthesis for your joins since SQL > doesn't need them. Access ... > This may sound horribly simple but I noticed a > change in your field naming convention throughout this thread. You have > category_id for all your tables except for your alias s4 > (auction_item_categories_sub4) where you have a field with an extra 'y' > categoryy_id So that is one error, and the other one is the missing alias in the predicate: SELECT * FROM auction_item_categories_sub5 s5 INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id = s4.id) INNER JOIN auction_item_categories_sub3 s3 ON s4.category_id = s3.id) INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id = s2.id) INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id) INNER JOIN auction_item_categories c ON s.category_id = c.id WHERE c.id = 1 Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190623 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
I'm not sure why you are using parenthesis for your joins since SQL doesn't need them. This may sound horribly simple but I noticed a change in your field naming convention throughout this thread. You have category_id for all your tables except for your alias s4 (auction_item_categories_sub4) where you have a field with an extra 'y' categoryy_id - could this be just a simple typo or was the deviation deliberate? Also, as a note on form, 1. do you really need SELECT * ? - enumerating the fields is always preferred over SELECT * 2. While Access doesn't support stored procs, you should consider using cfqueryparam instead of the hardcoded value to get the benefit of turning this call into a prepared statement, especially with this many joins involved. Subsequent calls can then take advantage of a cached execution plan. Try: SELECT * FROM auction_item_categories_sub5 s5 INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id = s4.id INNER JOIN auction_item_categories_sub3 s3 ON s4.categoryy_id = s3.id INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id = s2.id INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id INNER JOIN auction_item_categories c ON s.category_id = c.id WHERE auction_item_categories.id = 1 Regards, Adam Howitt Protoculture wrote: >Jochem. tried your code, but got the following error. > >Server Msg: -3010, State: 07002, [Microsoft][ODBC Microsoft Access Driver] > Too few parameters. Expected 2. > >with this code you supplied. > >SELECT * > >FROM auction_item_categories_sub5 s5 >INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id = > s4.id) >INNER JOIN auction_item_categories_sub3 s3 ON s4.categoryy_id = > s3.id) >INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id = > s2.id) >INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id) >INNER JOIN auction_item_categories c ON s.category_id = c.id > >WHERE auction_item_categories.id = 1 > > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190622 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
Jochem. tried your code, but got the following error. Server Msg: -3010, State: 07002, [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2. with this code you supplied. SELECT * FROM auction_item_categories_sub5 s5 INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id = s4.id) INNER JOIN auction_item_categories_sub3 s3 ON s4.categoryy_id = s3.id) INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id = s2.id) INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id) INNER JOIN auction_item_categories c ON s.category_id = c.id WHERE auction_item_categories.id = 1 >Protoculture Breetai wrote: > >SELECT id, category_id, name >FROM auction_item_categories_sub5 s5 > INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id >= s4.id) > INNER JOIN auction_item_categories_sub3 s3 ON s4.categoryy_id >= s3.id) > INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id >= s2.id) > INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id) > INNER JOIN auction_item_categories c ON s.category_id = c.id >WHERE auction_item_categories.id = 1 > >Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190619 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
Protoculture Breetai wrote: > thanks Joe, but I seem to still be getting a (missing operator) syntax error. > > SELECT id, category_id, name > > FROM auction_item_categories_sub5 > > INNER JOIN auction_item_categories_sub4 ON > auction_item_categories_sub5.category_id = auction_item_categories_sub4.id > INNER JOIN auction_item_categories_sub3 ON > auction_item_categories_sub4.categoryy_id = auction_item_categories_sub3.id > INNER JOIN auction_item_categories_sub2 ON > auction_item_categories_sub3.category_id = auction_item_categories_sub2.id > INNER JOIN auction_item_categories_sub ON > auction_item_categories_sub2.category_id = auction_item_categories_sub.id > INNER JOIN auction_item_categories ON > auction_item_categories_sub.category_id = auction_item_categories.id > where auction_item_categories.id = 1 SELECT id, category_id, name FROM auction_item_categories_sub5 s5 INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id = s4.id) INNER JOIN auction_item_categories_sub3 s3 ON s4.categoryy_id = s3.id) INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id = s2.id) INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id) INNER JOIN auction_item_categories c ON s.category_id = c.id WHERE auction_item_categories.id = 1 Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190602 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: multiple Inner Joins
> From: Protoculture Breetai [mailto:[EMAIL PROTECTED] > thanks Joe, but I seem to still be getting a (missing > operator) syntax error. > > SELECT id, category_id, name > > FROM auction_item_categories_sub5 > > INNER JOIN auction_item_categories_sub4 ON > auction_item_categories_sub5.category_id = > auction_item_categories_sub4.id > INNER JOIN auction_item_categories_sub3 ON > auction_item_categories_sub4.categoryy_id = > auction_item_categories_sub3.id > INNER JOIN auction_item_categories_sub2 ON > auction_item_categories_sub3.category_id = > auction_item_categories_sub2.id > INNER JOIN auction_item_categories_sub ON > auction_item_categories_sub2.category_id = > auction_item_categories_sub.id > INNER JOIN auction_item_categories ON > auction_item_categories_sub.category_id = auction_item_categories.id What table are the values in your SELECT clause coming from? ~| Purchase RoboHelp from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=59 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190601 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
thanks Joe, but I seem to still be getting a (missing operator) syntax error. SELECT id, category_id, name FROM auction_item_categories_sub5 INNER JOIN auction_item_categories_sub4 ON auction_item_categories_sub5.category_id = auction_item_categories_sub4.id INNER JOIN auction_item_categories_sub3 ON auction_item_categories_sub4.categoryy_id = auction_item_categories_sub3.id INNER JOIN auction_item_categories_sub2 ON auction_item_categories_sub3.category_id = auction_item_categories_sub2.id INNER JOIN auction_item_categories_sub ON auction_item_categories_sub2.category_id = auction_item_categories_sub.id INNER JOIN auction_item_categories ON auction_item_categories_sub.category_id = auction_item_categories.id where auction_item_categories.id = 1 >Joins are part of your "from" clause, not a seperate clause. This should help: > > >SELECT id, category_id, name > >FROM auction_item_categories_sub5 > INNER JOIN auction_item_categories_sub4 ON >auction_item_categories_sub5.category_id = >auction_item_categories_sub4.id > INNER JOIN auction_item_categories_sub3 ON >auction_item_categories_sub4.categoryy_id = >auction_item_categories_sub3.id > INNER JOIN auction_item_categories_sub2 ON >auction_item_categories_sub3.category_id = >auction_item_categories_sub2.id > INNER JOIN auction_item_categories_sub ON >auction_item_categories_sub2.category_id = >auction_item_categories_sub.id > INNER JOIN auction_item_categories ON >auction_item_categories_sub.category_id = auction_item_categories.id > > where auction_item_categories.id = 1 > >-joe > >On Sat, 15 Jan 2005 17:31:21 -0400, Protoculture Breetai ><[EMAIL PROTECTED]> wrote: >> ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190600 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: multiple Inner Joins
Joins are part of your "from" clause, not a seperate clause. This should help: SELECT id, category_id, name FROM auction_item_categories_sub5 INNER JOIN auction_item_categories_sub4 ON auction_item_categories_sub5.category_id = auction_item_categories_sub4.id INNER JOIN auction_item_categories_sub3 ON auction_item_categories_sub4.categoryy_id = auction_item_categories_sub3.id INNER JOIN auction_item_categories_sub2 ON auction_item_categories_sub3.category_id = auction_item_categories_sub2.id INNER JOIN auction_item_categories_sub ON auction_item_categories_sub2.category_id = auction_item_categories_sub.id INNER JOIN auction_item_categories ON auction_item_categories_sub.category_id = auction_item_categories.id where auction_item_categories.id = 1 -joe On Sat, 15 Jan 2005 17:31:21 -0400, Protoculture Breetai <[EMAIL PROTECTED]> wrote: > I am getting a 'missing operator error below'. I don't know what operator its > looking for. > > Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver] > Syntax error (missing operator) in query expression > 'auction_item_categories_sub5.category_id = auction_item_categories_sub4.id > INNER JOIN auction_item_categories_sub3 ON > auction_item_categories_sub4.categoryy_id = auction_item_categories_sub3.id > INNER JOIN auction_item_categories_sub2 ON auction_i'. > > SELECT id, category_id, name > > FROM auction_item_categories_sub5, > auction_item_categories_sub4, > auction_item_categories_sub3, > auction_item_categories_sub2, > auction_item_categories_sub, > auction_item_categories > > INNER JOIN auction_item_categories_sub4 ON > auction_item_categories_sub5.category_id = auction_item_categories_sub4.id > INNER JOIN auction_item_categories_sub3 ON > auction_item_categories_sub4.categoryy_id = auction_item_categories_sub3.id > INNER JOIN auction_item_categories_sub2 ON > auction_item_categories_sub3.category_id = auction_item_categories_sub2.id > INNER JOIN auction_item_categories_sub ON > auction_item_categories_sub2.category_id = auction_item_categories_sub.id > INNER JOIN auction_item_categories ON > auction_item_categories_sub.category_id = auction_item_categories.id > > where auction_item_categories.id = 1 > > ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190599 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
multiple Inner Joins
I am getting a 'missing operator error below'. I don't know what operator its looking for. Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'auction_item_categories_sub5.category_id = auction_item_categories_sub4.id INNER JOIN auction_item_categories_sub3 ON auction_item_categories_sub4.categoryy_id = auction_item_categories_sub3.id INNER JOIN auction_item_categories_sub2 ON auction_i'. SELECT id, category_id, name FROM auction_item_categories_sub5, auction_item_categories_sub4, auction_item_categories_sub3, auction_item_categories_sub2, auction_item_categories_sub, auction_item_categories INNER JOIN auction_item_categories_sub4 ON auction_item_categories_sub5.category_id = auction_item_categories_sub4.id INNER JOIN auction_item_categories_sub3 ON auction_item_categories_sub4.categoryy_id = auction_item_categories_sub3.id INNER JOIN auction_item_categories_sub2 ON auction_item_categories_sub3.category_id = auction_item_categories_sub2.id INNER JOIN auction_item_categories_sub ON auction_item_categories_sub2.category_id = auction_item_categories_sub.id INNER JOIN auction_item_categories ON auction_item_categories_sub.category_id = auction_item_categories.id where auction_item_categories.id = 1 ~| Protect Your PC from viruses, hackers, spam and more. Buy PC-cillin with Easy Installation & Support http://www.houseoffusion.com/banners/view.cfm?bannerid=61 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190596 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
