| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-08-10, 04:18
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 8
|
|
|
JOIN two tables, the second one parametric
|
|
Hello world,
this is my first post here.
I need to join 2 tables, say a and b, where b in fact "depends" on a. Of course there are several solution.
For example, this works:
SELECT a.field1, a.field2, (SELECT SUM(b.field3) FROM b WHERE b.somefield <= a.field1) FROM a
However, if I try to get more than 1 columns from b, it does not work anymore:
SELECT a.field1, a.field2, (SELECT SUM(b.field3),SUM(b.field4) FROM b WHERE b.somefield <= a.field1) FROM a
Do you know how to "expand" the 2 values from the second select into the first one?
Or, do you have some other solution?
Thank you.
|
|
11-08-10, 04:35
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Code:
SELECT a.field1
, a.field2
FROM a
INNER
JOIN ( SELECT somefield
, SUM(field3) AS sum_field3
, SUM(field4) AS sum_field4
FROM b
GROUP
BY somefield ) AS sumb
ON sumb.somefield <= a.field1
|
|
11-08-10, 06:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
An example:
Code:
------------------------------ Commands Entered ------------------------------
WITH
a(field1 , field2) AS (
VALUES
( 1 , 1)
, ( 5 , 2)
, (10 , 3)
, (15 , 4)
)
, b(column1 , somefield , field3 , field4) AS (
VALUES
(1 , 3 , 1 , 1)
, (2 , 5 , 4 , 2)
, (3 , 7 , 9 , 1)
, (4 , 11 , 16 , 2)
, (5 , 13 , 25 , 1)
, (6 , 17 , 36 , 2)
, (7 , 19 , 49 , 1)
)
SELECT a.field1
, a.field2
, SUM(b.field3) AS sum_3
, SUM(b.field4) AS sum_4
, COUNT(b.somefield) AS count_b
FROM a
LEFT OUTER JOIN
b
ON b.somefield <= a.field1
GROUP BY
a.field1
, a.field2
;
------------------------------------------------------------------------------
FIELD1 FIELD2 SUM_3 SUM_4 COUNT_B
----------- ----------- ----------- ----------- -----------
1 1 - - 0
5 2 5 3 2
10 3 14 4 3
15 4 55 7 5
4 record(s) selected.
|
|
11-08-10, 06:28
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Another example(with same data):
Code:
SELECT a.field1
, a.field2
, sum_3
, sum_4
, count_b
FROM a
CROSS JOIN
LATERAL
(SELECT SUM(b.field3) AS sum_3
, SUM(b.field4) AS sum_4
, COUNT(b.somefield) AS count_b
FROM b
WHERE b.somefield <= a.field1
)
;
------------------------------------------------------------------------------
FIELD1 FIELD2 SUM_3 SUM_4 COUNT_B
----------- ----------- ----------- ----------- -----------
1 1 - - 0
5 2 5 3 2
10 3 14 4 3
15 4 55 7 5
4 record(s) selected.
|
Last edited by tonkuma; 11-08-10 at 06:42.
Reason: Add result.
|
11-10-10, 03:21
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 8
|
|
Thank you all!
I am trying to make them work properly. I will let you know.
|
|
11-10-10, 03:41
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 8
|
|
@Tonkuma: your last example does not work on DB2. It gives a syntax error.
|
|
11-10-10, 04:04
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 8
|
|
...but your first example works! Thank you!
@r937: also your example has issues with db2 
|
|
11-10-10, 06:11
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
CROSS JOIN was supported from DB2 9.5 for LUW.
Nested table expression without correlation clause also supported from DB2 9.5 for LUW.
If you are using older DB2 version, you can use traditional comma syntax for join, like this...
Code:
SELECT a.field1
, a.field2
, sum_3
, sum_4
, count_b
/*
Replace "COROSS JOIN" with comma(",").
Add correlation name("b").
*/
FROM a
, LATERAL
(SELECT SUM(b.field3) AS sum_3
, SUM(b.field4) AS sum_4
, COUNT(b.somefield) AS count_b
FROM b
WHERE b.somefield <= a.field1
) b
;
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
JOIN two tables, the second one parametric
