Re: 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