如何从多个表中搜索父内容？

Question

表-家长

+----+----------+--------------+----------+
| id | user_id  | content_type |  name    |
+----+----------+--------------+----------+
|  1 | 10       | 1            | name - 1 |
|  2 | 12       | 2            | name - 2 |
+----+----------+--------------+----------+

content_type可以是

1 = Single part(movies)
2 = Multi prt(serials, episode)

表-儿童

+----+------------+--------------+--------------+-----------+
| id | parent_id  | is_episode   | episode_name | file_name |
+----+------------+--------------+--------------+-----------+
|  1 | 1          | 0            | NULL         | movie.mp4 |
|  2 | 2          | 0            | NULL         | zee.mp4   |
|  3 | 2          | 1            | cname-2      | lil-1.mp4 |
|  4 | 2          | 1            | child-2      | lil-2.mp4 |
+----+------------+--------------+--------------+-----------+

is_episode可以是：

0 = No
1 = Yes

注意:我跳过的其他一些字段

我的问题是：

SELECT F.id,F.user_id,F.content_type,F.name,M.is_episode, M.episode_name, M.file_name 
FROM childs M,parents F 
WHERE  M.parent_id = F.id AND M.is_episode=0 AND 
(F.name LIKE '%child%' OR M.episode_name LIKE '%child%' ) 

Abve MySQL查询我得到0条记录。有1个内容

我的搜索字符串是基于parents.name或childs.episode_name应用的，我想获取所有记录，如果我搜索is_episode=0，则查找当前记录的父内容。下面是我应该得到的输出

+----+----------+----------------+----------+------------+--------------+-----------+
| id | user_id  | content_type   | name     | is_episode | episode_name | file_name |
+----+----------+----------------+----------+------------+--------------+-----------+
|  2 | 12       | 2              | name - 2 | 0          | NULL         | zee.mp4   |
+----+----------+----------------+----------+------------+--------------+-----------+

输出：

In above my  two tables my output should come following criterias

如果我按name LIKE '%name%'进行搜索，输出应该是

+----+----------+----------------+----------+------------+--------------+
| id | user_id  | content_type   | name     | is_episode | episode_name |
+----+----------+----------------+----------+------------+--------------+
|  1 | 10       | 1              | name - 1 | 0          | NULL         |
|  2 | 12       | 2              | name - 2 | 0          | NULL         |
+----+----------+----------------+----------+------------+--------------+

如果按name LIKE '%cname%'、name LIKE '%name - 2%'或name LIKE '%child%'进行搜索，则输出应为

+----+----------+----------------+----------+------------+--------------+
| id | user_id  | content_type   | name     | is_episode | episode_name |
+----+----------+----------------+----------+------------+--------------+
|  2 | 12       | 2              | name - 2 | 0          | NULL         |
+----+----------+----------------+----------+------------+--------------+

如果我按name LIKE '%name - 1%'进行搜索，输出应该是

+----+----------+----------------+----------+------------+--------------+
| id | user_id  | content_type   | name     | is_episode | episode_name |
+----+----------+----------------+----------+------------+--------------+
|  1 | 10       | 1              | name - 1 | 0          | NULL         |
+----+----------+----------------+----------+------------+--------------+

如果我按name LIKE '%child%'进行搜索，输出应该是

+----+----------+----------------+----------+------------+--------------+
| id | user_id  | content_type   | name     | is_episode | episode_name |
+----+----------+----------------+----------+------------+--------------+
|  2 | 12       | 2              | name - 2 | 0          | NULL         |
+----+----------+----------------+----------+------------+--------------+

我无法获得我的查询的正确输出。你能写下对我所有的输出都满意的查询吗？

Answer 1

您的问题是，您只过滤掉了包含Is_episode = 0的行，但是您需要搜索包含is_episode = 1的行，如cname-2和child-2

你的问题是因为is_episode = 0引起的，删除它，它应该可以工作。如果您仍然有问题，请留言。

更新：根据你的评论，基于我的理解，我认为你可能需要一些基于table的多queries和分隔，或者你的logic/conditions，然后union结果一起。

select .. from parent, child .. where is_episode = 0 and name like ... 
union 
select .. from child join parent where episode_name like ...