Multiple join and sum query producing wrong output

SQL++
, ,
1

Hi

I have a bucket containing the following three documents:

coffee {
    id
}

cold_coffee {
    coffeeId
    number
}

warm_coffee {
    coffeeId
    number
}

I have written the following query:

SELECT META(coffee).id as coffeeId,
SUM(cold_coffee.`number`) as `ccNumber`,
SUM(warm_coffee.`number`) as `wcNumber`,
FROM coffee_bucket coffee
left JOIN coffee_bucket cold_coffee
    ON  META(coffee).id = cold_coffee.coffeeId 
    and cold_coffee.type='cold_coffee'
left JOIN coffee_bucket warm_coffee
    ON META(coffee).id = warm_coffee.coffeeId 
    and warm_coffee.type='warm_coffee'
where coffee.type='coffee'
group by META(coffee).id;

I have multiple cold_coffee and warm_coffee documents for every coffee and I need to sum the numbers for all of the cold_coffee and warm_coffee documents. The problem I am having is that if for example I have the following documents:

[
    coffee {
        id: 1
    },
    cold_coffee {
        coffeeId:1
        number:5 
    },
    cold_coffee {
        coffeeId:1
        number:5 
    },
    warm_coffee {
        coffeeId:1
        number:10
    }
]

My totals are the following:
ccNumber: 10
wcNumber:20

It seems because of the joins the warm_coffee document is being counted twice?

I came across this site with possibly the same error but unfortunately it’s SQL:

And I am unsure how to solve this using N1QL because the right hand term of a JOIN must be a table / bucket as seen in the following post on Stack Overflow:

Can someone please assist?

2

JOINS can expand the original rows. During first Join left most document is expanded. You are using first document field as second join condition that can produce multiple documents same condition. This how semantics works. You need to adjust your join condition based on the needs.

The second JOIN uses one document from the LEFT most. SUM over DISTINCT may not work because different documents same value (warm_coffee, may under count) is counted once. If I am right you are looking second join to use unique document from LEFT most coffee.

May be you are looking some thing like this

SELECT c.coffeeId,
       MAX(c.ccNumber) AS `ccNumber`,
       SUM(warm_coffee.`number`) AS `wcNumber`,
FROM ( SELECT META(coffee).id AS coffeeId,
       SUM(cold_coffee.`number`) AS `ccNumber`
       FROM coffee_bucket coffee
       LEFT JOIN coffee_bucket cold_coffee
            ON  META(coffee).id = cold_coffee.coffeeId AND cold_coffee.type='cold_coffee'
       WHERE coffee.type='coffee'
       GROUP BY META(coffee).id
     ) AS c
LEFT JOIN coffee_bucket warm_coffee
          ON c.coffeeId = warm_coffee.coffeeId AND warm_coffee.type='warm_coffee'
GROUP BY c.coffeeId;
1 Like
3

Thank you very much! This is working.

Here is a link to the same question I asked on Stack Overflow with a update for summing more than 2 documents.