CODEWITHSUNDEEP
sqlpostgresql
In0cenT
In0cenT

Reputation: 481

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 disposal and supply.

With the following query:

SELECT *
FROM projects
         JOIN disposals disposal on projects.project_id = disposal.disposal_project_refer
WHERE (projects.project_name = 'Höngg')

I get following result:

project_id,project_name,disposal_id,depository_refer,material_refer,disposal_date,disposal_measurement,disposal_project_refer
1,Test,1,1,1,2020-08-12 15:24:49.913248,123,1
1,Test,2,1,2,2020-08-12 15:24:49.913248,123,1
1,Test,7,2,1,2020-08-12 15:24:49.913248,123,1
1,Test,10,3,4,2020-08-12 15:24:49.913248,123,1

The same amount of results get returned by same query for supplies.

type Project struct {
    ProjectID   uint       `gorm:"primary_key" json:"ProjectID"`
    ProjectName string     `json:"ProjectName"`
    Disposals   []Disposal `gorm:"ForeignKey:disposal_project_refer"`
    Supplies    []Supply   `gorm:"ForeignKey:supply_project_refer"`
}

If I query both tables I would like to receive the sum of both single queries. Currently I am receiving 16 results (4 supply results multiplied by 4 disposal results).

The combined query:

SELECT *
FROM projects
         JOIN disposals disposal ON projects.project_id = disposal.disposal_project_refer
         JOIN supplies supply ON projects.project_id = supply.supply_project_refer
WHERE (projects.project_name = 'Höngg');

I have tried achieving my goal with union queries but I was not sucessfull. What else should I try to achieve my goal?

Upvotes: 0

Views: 81

Answers (1)

Abelisto
Abelisto

Reputation: 15624

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
    cross join lateral (
        select *
        from (select row_number() over() as rn, * from b where b.x=a.x) as b
                full join (select row_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
    cross join lateral (
        select *
        from (select row_number() over() as rn, * from b where b.x=a.x) as b
                full join (select row_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
    cross join lateral (
        select *
        from (select row_number() over() as rn, * from b where b.x=a.x) as b
                full join (select row_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:

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
    *,
    (select json_agg(to_json(b.*)) from b where a.x=b.x) as b,
    (select json_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}] │
└───┴───┴──────────────────────────────────────────────────┴────────────────────────────────────┘

Upvotes: 1

Related Questions