Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2010
    Posts
    8

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 07:42. Reason: Add result.

  5. #5
    Join Date
    Nov 2010
    Posts
    8

    Smile

    Thank you all!
    I am trying to make them work properly. I will let you know.

  6. #6
    Join Date
    Nov 2010
    Posts
    8
    @Tonkuma: your last example does not work on DB2. It gives a syntax error.

  7. #7
    Join Date
    Nov 2010
    Posts
    8
    ...but your first example works! Thank you!

    @r937: also your example has issues with db2

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •