Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    31

    Unanswered: Joining 2 tables without any key fields in DB2

    HI,

    I have to join 2 tables together but having difficulties to join them. For example,

    A.

    Table 1
    -----------
    Location Cust# Area_Code
    1 1111 201
    2 2222 202
    3 3333 301
    and so on

    Table 2
    ------------
    Area_Code
    201
    223
    431
    and so on

    Is there a way to join these two tables and get the location, cust#, and area_cd fields where table1.area_cd = table2.area_cd.

    Or

    B.

    Table 1
    ------------------
    Lcoation Cust# Home_phone cell_phone fax_phone
    1 1111 201-111-1111 null 231-111-1111
    2 2222 299-222-2222 599-123-3452 431-233-99999
    and so

    Table 2
    ------------
    Area_Code
    201
    223
    431
    and so on

    Is there a way to pick all the fields from table 1, if the the area_cd of home_phone, or cell_phone, or fax_phone matches with the area_cd from table 2.

    I would prefer example B as this is the way that data is available in the database. If not, then example A would be suffice as I can go one more step to put all the phones' area codes under one fields.

    Thanks in advance.

    Nav

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    look at pivot/listagg,etc... Once you have looked them up, show us the SQL that you come up with and we can give you some pointers.
    Last edited by dav1mo; 09-15-14 at 18:04.
    Dave

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select t1.*
    from table (values 
      (1, 1111, '201-111-1111', cast(null as varchar(20)), '231-111-1111')
    , (2, 2222, '299-222-2222', '599-123-3452', '431-233-99999')
    ) t1 (Lcoation, Cust#, Home_phone, cell_phone, fax_phone)
    join table (values 201, 223, 431) t2 (Area_Code) on t2.Area_Code in (
      int(substr(home_phone, 1, nullif(locate('-', home_phone), 0) - 1))
    , int(substr(cell_phone, 1, nullif(locate('-', cell_phone), 0) - 1))
    , int(substr(fax_phone,  1, nullif(locate('-', fax_phone),  0) - 1))
    )
    Regards,
    Mark.

  4. #4
    Join Date
    Jan 2010
    Posts
    31
    Thanks, it works. Nav

Posting Permissions

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