SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression-CodePudding

I have three tables as below:

Video Table id name 1 hello 2 world 3 new

Tag table

id   name
1    tag1
2    tag2
3    tag3

videoTag table

videoId    tagId
1          1
2          1
1          2
2          3

What i want is:

name       tags
hello      tag1, tag2
world      tag1, tag3

So, i have written the following query.

select
        string_agg(name, ', ')
    from
        tag t
    join "videoTag" vt on
        vt."tagId" = t."id"
    group by
        vt."videoId"
    order by
        vt."videoId"

This gives the following result

tags
tag1, tag2
tag1, tag3

I have the full query as:

with videoDetails("name",
"tags") as (
select
    v."name" as "name",
    (
    select
        string_agg(name, ', ')
    from
        tag t
    join "videoTag" vt on
        vt."tagId" = t."id"
    group by
        vt."videoId"
    order by
        vt."videoId" 
) as " tag"
from
    video v
join "videoTag" vt on
    vt."videoId" = v.id
join tag t on
    t.id = vt."tagId"
order by
    vt."videoId" 
    )
select
    *
from
    videoDetails;

So, I am getting the following error: SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

CodePudding user response：

The sub select is not needed (and will return more than one row, hence the error). I think you can simplify like this:

select
    v."name" as "name",
    string_agg(t."name", ', ' order by vt."videoId") as "tags"
from
    video v
join "videoTag" vt on
    vt."videoId" = v.id
join tag t on
    t.id = vt."tagId"
group by v."name";