Results 1 to 9 of 9

Thread: DB2 Query

  1. #1
    Join Date
    Jun 2012
    Posts
    4

    Unanswered: DB2 Query

    Hi All,

    I have scenario where I need use multiple tables..

    Code:
    Table1: Id, EmpId
    
    Table2: Id, Id1, EmpName
    
    Table3: Id1, Id2, Address
    
    Table4: Id2, Id3, PhoneNo
    
    Table4: Id3, Id4, State
    Now I need to select EmpId, EmpName, Address and PhoneNo where state is in my input list (Mostly one or two)

    I am kind of able to join the tables but I found Same EmpId has multiple EmpName because of spaces, special char, missing char..

    so I am getting one record for each EmpName but other information are duplicate..

    Can you Please let me know how to filter with only one name?

    I am using DB 9.7.5 Version

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are Ids(Id, Id1, Id2, Id3, Id4) in tables really that way?

    For example:
    Table3(Address) is related with Table2(EmpName), not directly relaed with Table1(EmpId).
    So, I couldn't understand why duplicate were there.

    Please review your keys(Id, Id1, Id2, Id3, Id4),
    or publish sample data(including Id(s) having duplicates and Id(s) having no duplicaes) and expected output from the data.

  3. #3
    Join Date
    Jun 2012
    Posts
    4
    Hi, I have given table and columns as sample. but they are used for different function..

    The first field of my example is unique and remaining might have duplicate.
    for example
    Code:
    Table1:
    1   1234
    2   1235
    3   1236
    4   1237
    5   1238
    
    Table2
    1234  NameId1 MyName1
    1235  NameId2 MyName2
    1236  NameId3 MyName3
    1237  NameId4 MyName4
    1234  NameId5 My'Name1
    
    Table3:
    NameId1 AddressId1 Address1
    NameId2 AddressId2 Address2
    NameId3 AddressId3 Address3
    NameId4 AddressId4 Address4
    NameId5 AddressId1 Address1
    
    etc...
    Now I need only 1234,1235,1236,1237 with MyName1 alone.. should not contain My'Name1

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Are you trying to "link" these entries? Something like a "bill of materials", but with employee data?

    What determined this was an acceptable way to design/implement?

  5. #5
    Join Date
    Jun 2012
    Posts
    4
    Yes. I am trying to link between these table. This was old system and need new report with this data

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1)
    How to choose (1234 NameId1 MyName1) rather than (1234 NameId5 My'Name1)?
    Note: There is no implicit row(record) sequence in a table. So "take first record" is no meaning in relaional dabase(SQL) world.
    But, you can say "choose arbitrary one row".

    (2)
    Even if AddressId1 was duplicaed in table3, one row of Table4 would be joined to each row in Table3,
    if AddressId(Id3) in Table4 was unique.
    But, if AddressId(Id3) in Table4 was not unique, how to choose one within duplicaed rows?
    Last edited by tonkuma; 06-27-12 at 17:48.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another issue:

    In your data,
    1234 in Table2 corresponds to (AddressId1 Address1) in Table3 one to one, even NameId(NameId1 and NameId5) were different.
    Is that always guaranteed for different Ids?

    Code:
    Table2
    1234  NameId1 MyName1
    ...
    1234  NameId5 My'Name1
    
    Table3:
    NameId1 AddressId1 Address1
    ...
    NameId5 AddressId1 Address1
    Last edited by tonkuma; 06-27-12 at 17:50.

  8. #8
    Join Date
    Jun 2012
    Posts
    4
    Sorry it typo...

    Correct values are as below
    Code:
    Table3:
    NameId1 AddressId1 Address1
    ...
    NameId5 AddressId5 Address1

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    From the information I understand now, I tried to make a query.
    If there were more conditions and/or exceptional data, please add more sample data and expected output.

    (Q1)
    How to choose (1234 NameId1 MyName1) rather than (1234 NameId5 My'Name1)?
    (A1)
    choose arbitrary one row.

    Example 1:

    Test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      Table1(Id , EmpId) AS (
    VALUES
      ( 1 , 1234 )
    , ( 2 , 1235 )
    , ( 3 , 1236 )
    , ( 4 , 1237 )
    , ( 5 , 1238 )
    )
    , Table2(EmpId , Id1 , EmpName) AS (
    VALUES
      ( 1234 , 'NameId1' , 'MyName1' )
    , ( 1235 , 'NameId2' , 'MyName2' )
    , ( 1236 , 'NameId3' , 'MyName3' )
    , ( 1237 , 'NameId4' , 'MyName4' )
    , ( 1234 , 'NameId5' , 'My''Name1' )
    )
    , Table3(Id1 , Id2 , Address) AS (
    VALUES
      ( 'NameId1' , 'AddressId1' , 'Address1' )
    , ( 'NameId2' , 'AddressId2' , 'Address2' )
    , ( 'NameId3' , 'AddressId3' , 'Address3' )
    , ( 'NameId4' , 'AddressId4' , 'Address4' )
    , ( 'NameId5' , 'AddressId5' , 'Address1' )
    )
    Query and output:
    Code:
    SELECT t1.Id
         , t1.EmpId
         , t2.Id1
         , t2.EmpName
         , t3.Id2
         , t3.Address
     FROM  Table1 t1
     INNER JOIN
          (SELECT t2.*
                , ROW_NUMBER()
                     OVER( PARTITION BY EmpId ) AS r_num
            FROM  Table2 t2
          ) t2
      ON   t2.EmpId = t1.EmpId
       AND t2.r_num = 1
     LEFT  OUTER JOIN /* or INNER JOIN ? */
           Table3 t3
      ON   t3.Id1 = t2.Id1
    ;
    ------------------------------------------------------------------------------
    
    ID          EMPID       ID1     EMPNAME  ID2        ADDRESS 
    ----------- ----------- ------- -------- ---------- --------
              1        1234 NameId1 MyName1  AddressId1 Address1
              2        1235 NameId2 MyName2  AddressId2 Address2
              3        1236 NameId3 MyName3  AddressId3 Address3
              4        1237 NameId4 MyName4  AddressId4 Address4
    
      4 record(s) selected.
    Last edited by tonkuma; 06-27-12 at 19:35.

Tags for this Thread

Posting Permissions

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