MySQL查询，用于搜索母子表中的字符串，并返回所有具有母子关系的行

Question

需要MySQL查询帮助。

请在下面找到架构：

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

此处"Table2"与主表"Table1"处于子关系。我想在上面两个表的标题和描述列中搜索。如果在主表"Table1"中找到搜索字符串，则应返回该行以及子表的所有行。类似地，如果在其中一个子表行中找到搜索字符串，则应返回相应的主表行及其所有子表。为了澄清，请查看以下结果：

结果：

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

是否可以通过单个查询或其他方式得到结果？

请帮忙。

Answer 1

尝试以下SQL：

更新的SQL

SELECT *
FROM Table1 
LEFT JOIN Table2 ON  Table1.PK_id = Table2.Fk_id
WHERE CONCAT(Table1_Title, ' ', Table1_Description) LIKE 'test2%'
UNION
SELECT *
FROM Table1 INNER JOIN Table2 ON  Table1.PK_id = Table2.Fk_id
WHERE Table1.PK_Id IN 
(
    SELECT Table2.Fk_id
    FROM Table2 
    WHERE CONCAT(Table2_Title, ' ', Table2_Description) LIKE 'test2%'
)

请参阅SqlFiddle

另一个变体SQL

SELECT *
FROM Table1 
LEFT JOIN Table2 ON  Table1.PK_id = Table2.Fk_id
WHERE Table1_Title LIKE 'test3%'
OR Table1_Description LIKE 'test3%'
OR Table1.PK_Id IN 
(
    SELECT Table2.Fk_id
    FROM Table2 
    WHERE CONCAT(Table2_Title, ' ', Table2_Description) LIKE 'test3%'
)

请参阅SqlFiddle

其中Test2是需要搜索的字符串。