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
