On 2010-10-21, at 7:38 PM, "Ron Piggott" <[email protected]> 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
