CODEWITHSUNDEEP
mysqlsql
Harsha Murupudi
Harsha Murupudi

Reputation: 573

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.

Small description of the requirment

Upvotes: 0

Views: 59

Answers (2)

VN'sCorner
VN'sCorner

Reputation: 1552

Complete query with populated test data is available in dbfiddle link.The solution is for SQL Server 2019 however should work on most of the other databases(Oracle,MySQL etc.)

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=99fc8959817dff9a83789da492041bcd

Upvotes: 1

rf1234
rf1234

Reputation: 1605

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

Upvotes: 1

Related Questions