How can I write a SQL query to do a full text search from multiple tables and then join the results comming from different tables?

I’m trying to do a text search from multiple tables searching in multiple columns and then join the results. But can’t figure out how to join the result. I did look at other post but couldn’t find a suitable solution.

In the below example I want to find all the court orders containing the keyword employment in either the title or the content. I need to search both the tables.

The tables are not related anyway.

Any help appreciated.

Answer

SELECT 'HighCourtOrder' as ORDER_TYPE, RULE_ID
  FROM HC_ORDER
 WHERE LOCATE("employment", TITLE) > 0
    OR LOCATE("employment", CONTENT) > 0
    UNION ALL
SELECT 'SupremeCourtOrder' as ORDER_TYPE, RULE_ID
  FROM SC_ORDER
 WHERE LOCATE("employment", TITLE) > 0
    OR LOCATE("employment", CONTENT) > 0    
ORDER BY 1, 2 ASC