Thanks for all the input: My code was correct when I compared it to the responses I received. I'm pretty new to mysql, so I was totally blaming my coding for the errors I was getting:
This is what I found.... I will stop using Microsoft for development of mysql. I was running the queries locally on my Win2k machine using the win32 version of mysql. After screwing with it for several days I finally gave up and posted to the list. My code works perfectly on my live Linux and FreeBSD servers. Now after several wasted days, I have another reason to hate Bill Gates! :-) Thanks again. -----Original Message----- From: Ralf Narozny [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 13, 2002 12:28 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Help needed with JOIN on 3 tables Hiho hiho! bob wrote: >Hi, > >I'm very new to mysql and I'm having a problem getting my joins to work. I >have 3 tables: > >builder >category >builder_category_link > >The builder table has a an id field and then several other fields (name, >address etc) >The category table has an id field and then one other field (type of >business... electrician, plumber etc) >The builder_category_link table has 3 fields, an id and then a builderid and >a categoryid (links the other two tables together) > >So, I can have a builder that can be a plumber, an electrician etc, etc. > >I've tried every way I can think to get the join to work and I've read and >re-read the mysql docs and >I just can't figure out how to make it go. I want to be able to search on >both builder name and category >(and get the builders that are in that category). > > I'm assuming the ID in builder table is named builderID in builder_category. Furthermore the ID in category is categoryID in builder_category_table. Then you should join like this: SELECT b.name, c.business FROM builder b LEFT JOIN builder_category bc ON ( b.id = bc.builderid) LEFT JOIN category c ON ( c.id = bc.builderid ) ; That should give out all names from builder which have any business given. Greetings Ralf -- Ralf Narozny Splendid Internet GmbH Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de --------------------------------------------------------------------- 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