我有一个带有tag_categories的表,我将它与其他一些表连接起来,这样我就可以获得文件标记的类别,在该表中,我有一列parent_id,它只是同一表中的另一个标记类别。在构建如下查询时,它确实检索到了parent_id,我不知道如何获取父名称:
查询:
select
tags.id,
tags.name,
tag_categories.name,
tag_categories.parent_id,
count(files.id)
from
"files"
inner join "file_tags_join" on "files"."public_id" = "file_tags_join"."file_public_id"
inner join "tags" on "file_tags_join"."tag_id" = "tags"."id"
inner join "tag_categories" on "tags"."category_id" = "tag_categories"."id"
where
"tags"."category_id" is not null
group by
tags.id,
tags.name,
tag_categories.name,
tag_categories.parent_id
除了我已经得到的之外,我希望得到的是与类别的parent_id匹配的类别的名称:
id name name parent_id count parent_name
2 tag2 tagCategory2 1 1 tagCategory1
1 tag1 tagCategory1 (null) 1 null
这是sql fiddle:
您可以在下面尝试
演示
select
tags.id,
tags.name,
tag_categories.name,
tag_categories.parent_id,
b.name as parentname,
count(files.id)
from
"files"
inner join "file_tags_join" on "files"."public_id" = "file_tags_join"."file_public_id"
inner join "tags" on "file_tags_join"."tag_id" = "tags"."id"
inner join "tag_categories" on "tags"."category_id" = "tag_categories"."id"
left join "tag_categories" b on tag_categories.parent_id = b.id
where
"tags"."category_id" is not null
group by
tags.id,
tags.name,
tag_categories.name,
tag_categories.parent_id,b.name
输出:
id name name parent_id parentname count
2 tag2 tagCategory2 1 tagCategory1 1
1 tag1 tagCategory1 1