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@xxxxxxxxx>
Sent: Thursday, October 21, 2010 8:03 PM
To: "Ron Piggott" <ron.piggott@xxxxxxxxxxxxxxxxxx>
Cc: "Artur Ejsmont" <ejsmont.artur@xxxxxxxxx>; <php-db@xxxxxxxxxxxxx>
Subject: Re: Creating an INDEX on multiple tables?
On 2010-10-21, at 7:38 PM, "Ron Piggott" <ron.piggott@xxxxxxxxxxxxxxxxxx>
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=
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
