Kysely•5mo ago

full join "using" list of columns

Hey, How can I create a full join which uses "using" I want to use "using" in the join but I only see "on", maybe I can acheive this using sqlusing , what is the correct way ? example:

with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select src__id,
       period,
       sum(amount_a) amount_a,
       sum(amount_b) amount_b,
       sum(amount_c) amount_c,
       sum(coalesce(amount_a,0)+coalesce(amount_b,0)-coalesce(amount_c,0)) as "A+B-C" 
from result1 
full outer join result2 using (src__id,period)
full outer join result3 using (src__id,period)
group by src__id,period;

with result1(src__id,period,amount_a) as (values ('id1','Q1',2) ),
     result2(src__id,period,amount_b) as (values ('id1','Q1',9),
                                                 ('id2','Q2',1) ),
     result3(src__id,period,amount_c) as (values ('id2','Q2',4))
select src__id,
       period,
       sum(amount_a) amount_a,
       sum(amount_b) amount_b,
       sum(amount_c) amount_c,
       sum(coalesce(amount_a,0)+coalesce(amount_b,0)-coalesce(amount_c,0)) as "A+B-C" 
from result1 
full outer join result2 using (src__id,period)
full outer join result3 using (src__id,period)
group by src__id,period;

Solution:

```ts .innerJoin('b', join => join .onRef('a.foo', '=', 'b.foo') .onRef('a.bar', '=', 'b.bar') )...

Jump to solution

9 Replies

koskimas•5mo ago

USING foo is just ON a.foo = b.foo USING (foo, bar) is just ON a.foo = b.foo AND a.bar = b.bar

Solution

koskimas•5mo ago

.innerJoin('b', join => join
  .onRef('a.foo', '=', 'b.foo')
  .onRef('a.bar', '=', 'b.bar')
)

.innerJoin('b', join => join
  .onRef('a.foo', '=', 'b.foo')
  .onRef('a.bar', '=', 'b.bar')
)

SneakOnYou•5mo ago

i tried it but I don't seem to get the same answer using "on" and using so something is missing because I try to join 3 tables and it does not have the same result set

koskimas•5mo ago

The second join is joining against first two tables

SneakOnYou•5mo ago

what do you mean ?

koskimas•5mo ago

Well you should probably know what your query is doing right? The second join joins against the joined set, not just a single table.

SneakOnYou•5mo ago

WITH result1(amount, src__id, period) AS (VALUES (76000, '006anGu2H5ygdjh5cK', 'FY2023-Q2'),
                                               (353000, '006bkoWWx3f7XK4kre', 'FY2023-Q3')),
     result2(amount, src__id, period) AS (VALUES (353000, '006bkoWWx3f7XK4kre', 'FY2023-Q2'),
                                               (320000, '006EAvTFuCNbXs8vfV', 'FY2023-Q2')),
     result3(amount, src__id, period) AS (VALUES (353000, '006bkoWWx3f7XK4kre', 'FY2023-Q3'),
                                               (320000, '006EAvTFuCNbXs8vfV', 'FY2023-Q2'))

SELECT COALESCE(r1.amount, 0) AS amount1,
       COALESCE(r2.amount, 0) AS amount2,
       COALESCE(r3.amount, 0) AS amount3,
       COALESCE(r1.src__id, COALESCE(r2.src__id, r3.src__id)) AS src__id,
       COALESCE(r1.period, COALESCE(r2.period, r3.period)) AS period,
       0 AS placeholder_column
FROM result1 r1
    full join result2 r2 using (src__id,period)
    full join result3 r3 using (src__id,period)
-- LEFT JOIN result2 r2 ON r1.src__id = r2.src__id AND r1.period = r2.period
-- LEFT JOIN result3 r3 ON r1.src__id = r3.src__id AND r1.period = r3.period

WITH result1(amount, src__id, period) AS (VALUES (76000, '006anGu2H5ygdjh5cK', 'FY2023-Q2'),
                                               (353000, '006bkoWWx3f7XK4kre', 'FY2023-Q3')),
     result2(amount, src__id, period) AS (VALUES (353000, '006bkoWWx3f7XK4kre', 'FY2023-Q2'),
                                               (320000, '006EAvTFuCNbXs8vfV', 'FY2023-Q2')),
     result3(amount, src__id, period) AS (VALUES (353000, '006bkoWWx3f7XK4kre', 'FY2023-Q3'),
                                               (320000, '006EAvTFuCNbXs8vfV', 'FY2023-Q2'))

SELECT COALESCE(r1.amount, 0) AS amount1,
       COALESCE(r2.amount, 0) AS amount2,
       COALESCE(r3.amount, 0) AS amount3,
       COALESCE(r1.src__id, COALESCE(r2.src__id, r3.src__id)) AS src__id,
       COALESCE(r1.period, COALESCE(r2.period, r3.period)) AS period,
       0 AS placeholder_column
FROM result1 r1
    full join result2 r2 using (src__id,period)
    full join result3 r3 using (src__id,period)
-- LEFT JOIN result2 r2 ON r1.src__id = r2.src__id AND r1.period = r2.period
-- LEFT JOIN result3 r3 ON r1.src__id = r3.src__id AND r1.period = r3.period

try this you get different results

koskimas•5mo ago

😄 That's what I'm saying. You need to join agains the whole joined set. Not just one table The second using join is joining agains the whole set at that point. Not just table a or b but both.

SneakOnYou•5mo ago

can I do raw sql inside the join ? is there any way of using "using" @koskimas ?

Gaming

Programming

full join "using" list of columns