Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    51

    Arrow Unanswered: Sql Query From Db2

    I Would Like To Join Two Tables:

    Select X, Y
    From Table1

    Select A, Z
    From Table2

    They Don't Have Any Field In Common, And I Want To Join
    Field Y = Field Z (when They Have The Same Value The Two
    Records Must Be Joined In A Single Record).

    Thank You

    Anna - Verona (italy)

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select X, Y, A, Z
    from Table1, Table2
    where Y = Z

    This may not work if Y and Z have different data types.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2007
    Posts
    51

    Sql query

    Excuse me but I didn't explain the matter correctly (the answer is in any
    case useful for future queries).
    What I really want to do is this:
    I want to select all the records from table1 and if one of its fields (field3) has the same value of fieldA in table2 I want to match the two records:
    For example:
    Table1
    field1 field2 field3
    sec.1 500 USD
    sec.2 300 EUR
    sec.3 400 GBP
    Table2
    fieldA fieldB
    USD 1,36
    GBP 0.678
    YEN 161,593
    RESULT:
    field1 field2 field3 fieldA fieldB
    sec.1 500 USD USD 1,36
    sec.2 300 EUR
    sec.3 400 GBP GBP 0.678

    Thank you for your attention.


    Anna - Verona (Italy)

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    I want to select all the records from Table1
    That will have to be a (left) outer join:
    Code:
    SELECT field1, field2, field3, fieldA, fieldB
    FROM   Table1 LEFT OUTER JOIN Table2
           ON field3 = fieldA
    It will indeed leave the entries fieldA and fieldB empty (NULL) when there is no match, but still show the Table1 rows:
    Code:
    field1 field2 field3 fieldA fieldB
    sec.1     500 USD    USD     1.36
    sec.2     300 EUR    ------ ------
    sec.3     400 GBP    GBP     0.678
    This means that you could even filter on the fieldA entries being NULL, i.e., not present in Table2, even if fieldA is a primary key of Table2 and hence never NULL in Table2:
    Code:
    SELECT field1, field2, field3
    FROM   Table1 LEFT OUTER JOIN Table2
           ON field3 = fieldA
    WHERE  fieldA IS NULL
    would return
    Code:
    field1 field2 field3
    sec.2     300 EUR
    which is exactly the same result as (but possibly more performant than) from the query
    Code:
    SELECT field1, field2, field3
    FROM   Table1
    WHERE  field3 NOT IN (SELECT fieldA FROM Table2)
    Last edited by Peter.Vanroose; 04-22-07 at 07:39.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Apr 2007
    Posts
    51
    Thank you so much, you are solving all my problems this morning!

    I want to ask you, with reference to the other thread I posted, you answered me to use the command COALESCE. Is it a command that can be used in SQL for DB2 queries? Or is it for SQLServer? And what about LEFT
    OUTER JOIN? I'm at home at the moment and I don't have QMf here so I
    can't try the commands.

    Thank you again.

    Anna - Verona (Italy)

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    ... the command COALESCE: is it a command that can be used in SQL for DB2 queries? And what about LEFT OUTER JOIN?
    Both are standerd SQL and can (nowadays) be used in any RDBMS, including DB2, SQLServer and Oracle.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Apr 2007
    Posts
    51
    Please help me again.

    This is a big query I'm making and problems come up keeping on thinking
    about it.

    I must select from three tables.
    The first is table1 we talked about in the previous post. I want always to select ALL THE RECORDS from this table.
    I want to select some fields from table2 which doesn't have values for all
    the records of table1 (like the previous case). I want to select field2.1 and field2.2 from table2 only if (field2.1 < chosen date and field2.2 > chosen date). Where shall I write this selection?;
    Now I have all records of table1 with some records bearing data of table2.
    Then I have table3 which is table2 of the previous post. I want to outer join this table .
    For example:
    Table1
    field1 field2 field3
    sec.1 500 USD
    sec.2 300 EUR
    sec.3 400 GBP
    Table2
    field1a field 2a field 2b
    sec.2 20070210 20070410
    sec.3 20070415 20071015
    Table3
    fieldA fieldB
    USD 1,36
    GBP 0.678
    YEN 161,593
    DESIRED RESULT:
    field1 field2 field3 field2a field2b fieldBA
    sec.1 500 USD 1,36
    sec.2 300 EUR 20070210 20070410
    sec.3 400 GBP 20070415 20071015 0.678

    How will I write the 'from' clause?
    select from table1 left outer join table2 left outer join table 3. Is it okay?
    Or maybe instead of the first left outer join I could write:
    select
    field1, ......,
    case when (field2a < chosen date and field 2b > chosen date) as field2a and field2b (?????)
    from table1, table2 left outer join table3 (excuse me if I dare...)

    Another question: are there problems with left outer joins if I have nested tables inside the query?

    Thank you. I promise this is my last post for today.

    Anna - Italy (Verona)
    Last edited by annamaria; 04-22-07 at 12:05.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    DESIRED RESULT:
    field1 field2 field3 field2a field2b fieldBA
    sec.1 500 USD 1,36
    sec.2 300 EUR 20070210 20070410
    sec.3 400 GBP 20070415 20071015 0.678

    select from table1 left outer join table2 left outer join table 3. Is it okay?
    That's right: if no rows of table1 should be filtered away, you must LEFT OUTER JOIN it with all subsequent tables.

    Now for the join condition:

    If you would filter after joining, some rows of table1 could disappear, so there should not be any WHERE conditions, at least not in the outer query.

    Instead, first reduce table2 to its minimal proportions, filtering away any unnecessary rows and/or columns:
    Code:
    SELECT field2a, field2b
    FROM   table2
    WHERE  <some condition>
    Now, outer join table1 to this reduced table:
    Code:
    SELECT field1, field2, field3, field2a, field2b, fieldB
    FROM   table1
           LEFT OUTER JOIN
           (SELECT field1a, field2a, field2b
            FROM   table2
            WHERE  <some condition>) AS t2
           ON table1.field1 = t2.field1a
           LEFT OUTER JOIN table3
           ON field3 = fieldA
    As you can see, I had to add field1a to the t2 definition in order to be able to join it to table1.

    (Not that, with DB2 version 8, you could equivalently use a common table expression for t2; this would improve readability.)
    Last edited by Peter.Vanroose; 04-22-07 at 16:22.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by Peter.Vanroose
    (Not that, with DB2 version 8, you could equivalently use a common table expression for t2; this would improve readability.)
    .
    What do you mean by 'common table expression'?

    Don't you know if nested tables (i.e. select from (select from....) cause problems to outer joins?

    Bye

    Anna - Verona (Italy)

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by annamaria
    What do you mean by 'common table expression'?
    Here is the CTE version of my previous query:
    Code:
    WITH t2 AS
    (SELECT field1a, field2a, field2b
     FROM   table2
     WHERE  <some condition>) 
    SELECT field1, field2, field3, field2a, field2b, fieldB
    FROM   table1  LEFT OUTER JOIN  t2
           ON field1 = field1a
           LEFT OUTER JOIN table3
           ON field3 = fieldA
    So it's a syntactic alternative for a nested table expression.
    Quote Originally Posted by annamaria
    Do you know if nested tables (i.e. select from (select from....) cause problems to outer joins?
    No, they don't.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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