K
Kysely•11mo ago
SneakOnYou

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
koskimas•11mo 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
koskimas•11mo 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
SneakOnYouOP•11mo 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
koskimas•11mo ago
The second join is joining against first two tables
SneakOnYou
SneakOnYouOP•11mo ago
what do you mean ?
koskimas
koskimas•11mo 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
SneakOnYouOP•11mo 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
koskimas•11mo 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
SneakOnYouOP•11mo ago
can I do raw sql inside the join ? is there any way of using "using" @koskimas ?
Want results from more Discord servers?
Add your server