Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Posts
    84

    Unanswered: Need Help in cartsian product

    Hi can you help me on this how to make a cartesian product in two tables,is this displaying all the columns in the table_A full join Table_b is this a cartesian product meant?please help me thank you in advance and i am hoping for your positive response.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    See the syntax and description of joined-table.
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

    joined-table

    Code:
                          .-INNER-----.                                                
    >>-+-table-reference--+-----------+--JOIN--table-reference--ON--join-condition-+-><
       |                  '-| outer |-'                                            |   
       +-table-reference--CROSS JOIN--table-reference------------------------------+   
       '-(--joined-table--)--------------------------------------------------------'   
    
    outer
    
                  .-OUTER-.   
    |--+-LEFT--+--+-------+-----------------------------------------|
       +-RIGHT-+              
       '-FULL--'
    A joined table specifies an intermediate result table that is the result of either an inner join or an outer join. The table is derived by applying one of the join operators: CROSS, INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER to its operands.

    Cross joins represent the cross product of the tables, where each row of the left table is combined with every row of the right table. ...
    ...
    You can make a cartesian product by
    table_a CROSS JOIN table_b

    Another not so apparent way may be
    table_a JOIN table_b ON 0=0

  3. #3
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    See the syntax and description of joined-table.
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows



    You can make a cartesian product by
    table_a CROSS JOIN table_b

    Another not so apparent way may be
    table_a JOIN table_b ON 0=0

    you mean like this,correct me if i am wrong

    select A.col1,A.col2,A.col3,B.col1,B.col2,B.col3
    from Table_A A cross join Table_B B
    Last edited by jemz; 08-17-11 at 01:42.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    select count(sir) from dbforums.db2 where user = 'jemz'

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm sorry!
    I have no authority to edit the jemz's posts.

    I'm not sure the error message nor SQLSTATE.

    UPDATE dbforums.db2 SET post = REPLACE(post , 'sir' , '') where user = 'jemz'

    SQL0551N "TONKUMA" does not have the required authorization or privilege to
    perform operation "UPDATE" on object "DBFORUMS.DB2". SQLSTATE=42501
    Last edited by tonkuma; 08-16-11 at 22:29.

  6. #6
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    I'm sorry!
    I have no authority to edit the jemz's posts.

    I'm not sure the error message nor SQLSTATE.

    UPDATE dbforums.db2 SET post = REPLACE(post , 'sir' , '') where user = 'jemz'

    SQL0551N "TONKUMA" does not have the required authorization or privilege to
    perform operation "UPDATE" on object "DBFORUMS.DB2". SQLSTATE=42501

    Hi, Tonkuma i apologize if i call you sir,...i will remove all the sir that addresses to you....I hope this will be okay now.

  7. #7
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    See the syntax and description of joined-table.
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows



    You can make a cartesian product by
    table_a CROSS JOIN table_b

    Another not so apparent way may be
    table_a JOIN table_b ON 0=0


    Hi tonkuma i get back to this thread i am confuse with this,

    table_a cross join table_b
    do i need the where clause?

    because if i only use
    table_a CROSS JOIN table_b
    i got 450 rows to display but if i specify the where clause

    a.column = b.column

    i got only 17 records... is this correct?please help me on this.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... if i only use
    table_a CROSS JOIN table_b
    i got 450 rows to display but if i specify the where clause

    a.column = b.column

    i got only 17 records... is this correct?please help me on this.
    It must be worked exactly as definition of joins and roles of where clause.

    I can't explain more accurately and detailed than in manuals.
    Please see carefully syntax and descriptions of subselect.
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows


    What issue do you have in the results?
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table_a(column) AS (
    VALUES
        1, 2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18
    )
    ,table_b(column) AS (
    VALUES
           2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18,19,20
      ,21,22,23,24,25,26
    )
    SELECT COUNT(*) AS count_rows
     FROM  table_a a CROSS JOIN table_b b
    -- WHERE a.column = b.column
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS 
    -----------
            450
    
      1 record(s) selected.
    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table_a(column) AS (
    VALUES
        1, 2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18
    )
    ,table_b(column) AS (
    VALUES
           2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18,19,20
      ,21,22,23,24,25,26
    )
    SELECT COUNT(*) AS count_rows
     FROM  table_a a CROSS JOIN table_b b
     WHERE a.column = b.column
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS 
    -----------
             17
    
      1 record(s) selected.
    Last edited by tonkuma; 08-23-11 at 16:41. Reason: Add examples.

  9. #9
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    It must be worked exactly as definition of joins and roles of where clause.

    I can't explain more accurately and detailed than in manuals.
    Please see carefully syntax and descriptions of subselect.
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows


    What issue do you have in the results?
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table_a(column) AS (
    VALUES
        1, 2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18
    )
    ,table_b(column) AS (
    VALUES
           2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18,19,20
      ,21,22,23,24,25,26
    )
    SELECT COUNT(*) AS count_rows
     FROM  table_a a CROSS JOIN table_b b
    -- WHERE a.column = b.column
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS 
    -----------
            450
    
      1 record(s) selected.
    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table_a(column) AS (
    VALUES
        1, 2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18
    )
    ,table_b(column) AS (
    VALUES
           2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18,19,20
      ,21,22,23,24,25,26
    )
    SELECT COUNT(*) AS count_rows
     FROM  table_a a CROSS JOIN table_b b
     WHERE a.column = b.column
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS 
    -----------
             17
    
      1 record(s) selected.
    Hi tonkuma,thank you for the reply,i am confuse what should i use when making the cartesian product of the 2 tables...

    I attached the problem of cartesian,the table is to be used is the one that i send you before in other thread...please help me sir


    please see the attachment
    Last edited by jemz; 08-24-11 at 02:41.

  10. #10
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    It must be worked exactly as definition of joins and roles of where clause.

    I can't explain more accurately and detailed than in manuals.
    Please see carefully syntax and descriptions of subselect.
    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows


    What issue do you have in the results?
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table_a(column) AS (
    VALUES
        1, 2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18
    )
    ,table_b(column) AS (
    VALUES
           2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18,19,20
      ,21,22,23,24,25,26
    )
    SELECT COUNT(*) AS count_rows
     FROM  table_a a CROSS JOIN table_b b
    -- WHERE a.column = b.column
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS 
    -----------
            450
    
      1 record(s) selected.
    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table_a(column) AS (
    VALUES
        1, 2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18
    )
    ,table_b(column) AS (
    VALUES
           2, 3, 4, 5, 6, 7, 8, 9,10
      ,11,12,13,14,15,16,17,18,19,20
      ,21,22,23,24,25,26
    )
    SELECT COUNT(*) AS count_rows
     FROM  table_a a CROSS JOIN table_b b
     WHERE a.column = b.column
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS 
    -----------
             17
    
      1 record(s) selected.

    Hi Tonkuma, i send you pm,please read it.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Usually, you need not to use cartesian product.

    Use it when you can't get expected result with inner join or outer join.

    I'm not so interesting in this subject, now.

Posting Permissions

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