search multiple tables with sql

Hi I'm trying to search multiple tables with sql, being used in a asp web page.

I need to connect to about 5 tables and search all of them on our search page. Here is the code that I have used but it dose not work.

SELECT *
FROM creek_aboutus AS ca, creek_dealers AS cd, creek_distributors AS cds, creek_events AS ce, creek_news AS cn, creek_products AS cp
WHERE ca.my_aboutus_name + cd.my_area + cds.my_country + ce.my_event_text + cn.my_news_text + cp.my_pro_text = 'MMColParam';


Can anyone help?

I have also tryed this to but I get an error as it says you can't use different tables when using the UNION ALL.


SELECT *
FROM creek_aboutus
UNION ALL
SELECT *
FROM creek_dealers
ORDER BY 'MMColParam'