英文:
Combining inner join query result with select query result in Mysql
问题
我正在使用MySQL。我需要将一个连接查询结果与另一个选择查询结果合并。详细信息如下：
表一 "TaskCategory" 具有以下列：
cat_id
parent_id
name
type
我将类别任务和子类别任务都存储在同一张表中。类别任务的 parent_id 为 0，而子类别任务的 parent_id 为其类别 id。
我有另一张表 TaskCategoryMeta。该表具有以下列：
cat_id
meta_name
meta_value
只有子类别会在 TaskCategoryMeta 表中有行/条目。
在结果中，我需要具有 TaskCategoryMeta 表中特定 meta 名称和 meta 值的子类别，以及 TaskCategory 表中父 id 为 0 的所有类别。
我尝试了下面的查询，但出现了查询中断错误，请帮助我实现我的结果。
SELECT *
FROM TaskCategory
INNER JOIN TaskCategoryMeta
ON TaskCategory.cat_id = TaskCategoryMeta.cat_id
WHERE TaskCategoryMeta.meta_name = 'HR_DEPT_SUPPORT'
AND TaskCategoryMeta.meta_value = 1
UNION
SELECT * FROM TaskCategory WHERE TaskCategory.parent_id = 0 AND type = 1
英文:
I am using MySQl.I need to combine one join query result with another select query result. The details are,
Table one "TaskCategory" has below columns
cat_id
parent_id
name
type
I am storing both category task and subcategory tasks in same table. The category task would have parent_id as 0 and subcategory task would parent_id as the id of it's category id
I have another table TaskCategoryMeta. This table has below columns
cat_id,
meta_name.
meta_value
Only subcategories would have rows/entries in the TaskCategoryMeta table.
In the result I need the subcategories with certain meta name and meta value in TaskCategoryMeta table and all categories with parent id of 0 from TaskCategory table.
I tried below query but I got query interrupted error, please help me to achieve my result.
SELECT *
FROM TaskCategory
INNER JOIN TaskCategoryMeta
ON TaskCategory.cat_id = TaskCategoryMeta.cat_id where TaskCategoryMeta.cat_meta_name='HR_DEPT_SUPPORT'
and TaskCategoryMeta.cat_meta_value=1
UNION
select * from TaskCategory where TaskCategory.cat_parent_id=0 and cat_type=1
答案1
得分: 1
不需要2个查询，但我认为你需要在where子句内有2个“部分”。要做到这一点，你需要使用括号将条件“绑定”在一起，然后在这2个部分之间引入一个“OR”。请注意，缩进总是有助于更容易理解括号包含的内容，例如：
SELECT *
FROM TaskCategory
LEFT JOIN TaskCategoryMeta ON TaskCategory.cat_id = TaskCategoryMeta.cat_id
WHERE (
TaskCategoryMeta.cat_meta_name = 'HR_DEPT_SUPPORT'
AND TaskCategoryMeta.cat_meta_value = 1
)
OR (
TaskCategory.cat_parent_id = 0
AND cat_type = 1 -- 你没有告诉我们这个字段来自哪个表
)
正如Swifty在下面的评论中指出的那样，你不能使用内连接，因为元数据表中只有子类别任务（对我来说这似乎是一个奇怪的设计特点）。因此，改为使用左（外）连接允许类别任务出现在结果中。
请注意，cat_type 应该由源表名称前缀。
英文:
You don't need 2 queries, but I believe you need 2 "sections" within the where clause. To do that you need to "bind" conditions together using parentheses, and, then introduce an OR between the 2 sections. Note it is always helpful to indent so it is easier to understand what the parentheses contain e.g:
SELECT *
FROM TaskCategory
LEFT JOIN TaskCategoryMeta ON TaskCategory.cat_id = TaskCategoryMeta.cat_id
WHERE (
TaskCategoryMeta.cat_meta_name = 'HR_DEPT_SUPPORT'
AND TaskCategoryMeta.cat_meta_value = 1
)
OR (
TaskCategory.cat_parent_id = 0
AND cat_type = 1 -- you haven't told us which table this comes from
)
As Swifty points out in a comment below, you can't use an inner join as only subcategory tasks are present in the meta table (which to me seems an odd design feature). So, instead, a left (outer) join allows for category tasks to be present in the results.
Please note cat_type should be prefixed by the source table name
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论