MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

MySQL

Need MySQL query Help.

Please find schema below:

Table1
PK_id    Table1_Title    Table1_Description
1        Test1           This is Test1
2        Test2           This is Test2

Table2
FK_id    Table2_Title    Table2_Description
1        Test2           This is Test2  
1        Test3           This is Test3

Here "Table2" is in child relationship with Master table "Table1". I want to search in Title and Description columns of above 2 tables. If search string is found in master table "Table1" then that row along with all the rows of child should be returned. Simillarly if search string is found in one of the child tabel row then the coresponding Master table row and all its child should be returned. To clarify please find result below:

Result:

Case 1:
If searching TEST2 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3
2        Test2           This is Test2         NULL     NULL            NULL


Case 2:
If searching TEST1 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3


Case 3:
If searching TEST3 Then

PK_id    Table1_Title    Table1_Description    FK_id    Table2_Title    Table2_Description
1        Test1           This is Test1         1        Test2           This is Test2
1        Test1           This is Test1         1        Test3           This is Test3

Is it possible to have a result with single query or something else?

Please help.

Best Answer

SELECT  t1.*, 
        t2.*

FROM    Table1 AS t1
    LEFT JOIN
        Table2 AS t2
            ON WHERE t2s.FK_id = t1.PK_id

WHERE   t1.Table1_Title = 'search text'
   OR   EXISTS
        ( SELECT  *
          FROM    Table2 AS t2s
          WHERE   t2s.FK_id = t1.PK_id
            AND   t2s.Table2_Title = 'search text'
        ) ;
Related Question