Re: Creating an INDEX on multiple tables?

Very much so. Thank you Bastien. I have never used an OUTER join before and didn't know it would be required for this project. Ron -------------------------------------------------- From: "Bastien" <phpster@gmail.com> Sent: Thursday, October 21, 2010 8:03 PM To: "Ron Piggott" <ron.piggott@actsministries.org> Cc: "Artur Ejsmont" <ejsmont.artur@gmail.com>; <php-db@lists.php.net> Subject: Re: [PHP-DB] Creating an INDEX on multiple tables?

On 2010-10-21, at 7:38 PM, "Ron Piggott" <ron.piggott@actsministries.org> wrote:

I am still struggling with this query still although I have made great progress. The following query (below) executes successfully. Right now the query returns no rows. I believe this is because there isn't automatically a record in the following three tables. ministry_profiles_activity ministry_profile_categories ministry_profiles_listing_details The bare minimum for a listing is only a record in he table ministry_profiles Is there a way to modify this query to accommodate only a record in the table ministry_profiles Ron? SELECT ministry_profiles.reference, ministry_profiles.organization FROM ( ( ( ministry_profiles INNER JOIN ministry_profiles_listing_details ON ministry_profiles.reference = ministry_profiles_listing_details.ministry_profile_reference ) INNER JOIN ministry_profiles_activity ON ministry_profiles.reference = ministry_profiles_activity.ministry_profiles_reference ) INNER JOIN ministry_profile_categories ON ministry_profiles.reference = ministry_profile_categories.ministry_profiles_reference ) INNER JOIN ministry_categories ON ministry_profile_categories.ministry_categories_reference = ministry_categories.reference WHERE MATCH( ministry_profiles.organization, ministry_profiles.address_line_1, ministry_profiles.address_line_2, ministry_profiles.city, ministry_profiles.province_state, ministry_profiles.postal_zip_code, ministry_profiles.country, ministry_profiles.telephone, ministry_profiles.toll_free, ministry_profiles.fax, ministry_profiles.email, ministry_profiles.website ) AGAINST ('$search')

Ron, Try using a left outer join from the table that has records. The inner join won't return any rows if one record in one table is null. A left outer join will take all records from the left (first) table regardless of data being null in the other rows. Does that make sense? Bastien=