The query works using UNION ALL and MATCH ( ) AGAINST --- one table at a
time for the MATCH. The down side is that each SELECT is giving it's own
search results. The directory listings are being displayed multiple times
when one of the SELECTS produces it as a result. How do I limit the
results? Can I do this some how with results.reference and
results.organization (I made the UNION ALLs sub queries)
Thanks for the help.
Ron
SELECT reference, organization FROM (
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER 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')
AND
`ministry_profiles`.`live` =1
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_categories`.`category` )
AGAINST
('$search')
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles_activity`.`activity` )
AGAINST
('$search')
AND `ministry_profiles_activity`.`live` =1
GROUP BY `ministry_profiles`.`reference`
UNION ALL
SELECT `ministry_profiles`.`reference`, `ministry_profiles`.`organization`
FROM
(
(
( `ministry_profiles` LEFT OUTER JOIN `ministry_profiles_listing_details` ON
`ministry_profiles`.`reference` =
`ministry_profiles_listing_details`.`ministry_profile_reference` )
LEFT OUTER JOIN `ministry_profiles_activity` ON
`ministry_profiles`.`reference` =
`ministry_profiles_activity`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_profile_categories` ON
`ministry_profiles`.`reference` =
`ministry_profile_categories`.`ministry_profiles_reference` )
LEFT OUTER JOIN `ministry_categories` ON
`ministry_profile_categories`.`ministry_categories_reference` =
`ministry_categories`.`reference`
WHERE
MATCH( `ministry_profiles_listing_details`.`contact`,
`ministry_profiles_listing_details`.`year_founded`,
`ministry_profiles_listing_details`.`volunteer_opportunities`,
`ministry_profiles_listing_details`.`employment_opportunities`,
`ministry_profiles_listing_details`.`members_of`,
`ministry_profiles_listing_details`.`major_events`,
`ministry_profiles_listing_details`.`associate_member_of`,
`ministry_profiles_listing_details`.`registration_number`,
`ministry_profiles_listing_details`.`fund_raising` )
AGAINST
('$search')
GROUP BY `ministry_profiles`.`reference`
) AS results
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
