Sql Query Returns Product Of Results Instead Of Sum

How can I make sure that with this join I'll only receive the sum of results and not the product? I have a project entity, which contains two one-to-many relations. If I query disp

Solution 1:

It is your case (simplified):

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22)), c(x,t) as (values(1,111),(1,222))
select * from a join b on (a.x=b.x) join c on (b.x=c.x);
┌───┬───┬───┬────┬───┬─────┐
│ x │ y │ x │ z  │ x │  t  │
├───┼───┼───┼────┼───┼─────┤
│ 1 │ 1 │ 1 │ 11 │ 1 │ 111 │
│ 1 │ 1 │ 1 │ 11 │ 1 │ 222 │
│ 1 │ 1 │ 1 │ 22 │ 1 │ 111 │
│ 1 │ 1 │ 1 │ 22 │ 1 │ 222 │
└───┴───┴───┴────┴───┴─────┘

It produces cartesian join because the value for join is same in all tables. You need some additional condition for joining your data.For example (tests for various cases):

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22)), c(x,t) as (values(1,111),(1,222))
select*from a
    crossjoinlateral (
        select*from (selectrow_number() over() as rn, *from b where b.x=a.x) as b
                fulljoin (selectrow_number() over() as rn, *from c where c.x=a.x) as c on (b.rn=c.rn)
        ) as bc;

┌───┬───┬────┬───┬────┬────┬───┬─────┐
│ x │ y │ rn │ x │ z  │ rn │ x │  t  │
├───┼───┼────┼───┼────┼────┼───┼─────┤
│ 1 │ 1 │  1 │ 1 │ 11 │  1 │ 1 │ 111 │
│ 1 │ 1 │  2 │ 1 │ 22 │  2 │ 1 │ 222 │
└───┴───┴────┴───┴────┴────┴───┴─────┘

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22),(1,33)), c(x,t) as (values(1,111),(1,222))
select*from a
    crossjoinlateral (
        select*from (selectrow_number() over() as rn, *from b where b.x=a.x) as b
                fulljoin (selectrow_number() over() as rn, *from c where c.x=a.x) as c on (b.rn=c.rn)
        ) as bc;

┌───┬───┬────┬───┬─────┬──────┬──────┬──────┐
│ x │ y │ rn │ x │  z  │  rn  │  x   │  t   │
├───┼───┼────┼───┼─────┼──────┼──────┼──────┤
│ 1 │ 1 │  1 │ 1 │  11 │    1 │    1 │  111 │
│ 1 │ 1 │  2 │ 1 │  22 │    2 │    1 │  222 │
│ 1 │ 1 │  3 │ 1 │  33 │ ░░░░ │ ░░░░ │ ░░░░ │
└───┴───┴────┴───┴─────┴──────┴──────┴──────┘

# with a(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22)), c(x,t) as (values(1,111),(1,222),(1,333))
select*from a
    crossjoinlateral (
        select*from (selectrow_number() over() as rn, *from b where b.x=a.x) as b
                fulljoin (selectrow_number() over() as rn, *from c where c.x=a.x) as c on (b.rn=c.rn)
        ) as bc;

┌───┬───┬──────┬──────┬──────┬────┬───┬─────┐
│ x │ y │  rn  │  x   │  z   │ rn │ x │  t  │
├───┼───┼──────┼──────┼──────┼────┼───┼─────┤
│ 1 │ 1 │    1 │    1 │   11 │  1 │ 1 │ 111 │
│ 1 │ 1 │    2 │    1 │   22 │  2 │ 1 │ 222 │
│ 1 │ 1 │ ░░░░ │ ░░░░ │ ░░░░ │  3 │ 1 │ 333 │
└───┴───┴──────┴──────┴──────┴────┴───┴─────┘

db<>fiddle

Note that there is no any obvious relations between disposals and supplies (b and c in my example) so the order of both could be random. As for me the better solution for this task could be the aggregation of the data from those tables using JSON for example:

witha(x,y) as (values(1,1)), b(x,z) as (values(1,11),(1,22),(1,33)), c(x,t) as (values(1,111),(1,222))
select
    *,
    (select json_agg(to_json(b.*)) from b where a.x=b.x) as b,
    (selectjson_agg(to_json(c.*)) from c where a.x=c.x) as c
from a;

┌───┬───┬──────────────────────────────────────────────────┬────────────────────────────────────┐
│ x │ y │                        b                         │                 c                  │
├───┼───┼──────────────────────────────────────────────────┼────────────────────────────────────┤
│ 1 │ 1 │ [{"x":1,"z":11}, {"x":1,"z":22}, {"x":1,"z":33}] │ [{"x":1,"t":111}, {"x":1,"t":222}] │
└───┴───┴──────────────────────────────────────────────────┴────────────────────────────────────┘