Results 1 to 10 of 10

Thread: Table Join

  1. #1
    Join Date
    Jun 2006
    Posts
    22

    Question Unanswered: Table Join

    TABLE1 is a listing of all employees with EmpID as the primary key.

    TABLE2 is a listing of all the time employees charge with no primary key but it does have a matching EmpID column.

    I want to join the tables by EmpID and group by PayEndDate, BudgetNum, sum(hours), count(EmpID).

    I want to count only the EmpID from TABLE1 as it is unique and there is only one but my query ends up counting all the entries matched in the other table (multiple EmpIDs per PayEndDate in TABLE2). How would I do this? Any help is appreciated.

    Edit: I'm trying to display the following:

    PayEndDate BudgetNum sum(hours) count(EmpID)
    1/3/09 101 3569.2 157

    On a side note, I'm just an end user and unable to change the setup although I would if I could.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please show your entire query(SELECT statement) and write DB2 version/release and platform(OS) on your DB2 server is running.

  3. #3
    Join Date
    May 2006
    Posts
    20
    Are you trying to pull the report of EMPID who is having latest payend date?
    And I believe below is the table columns you have,

    Table1
    EMPID
    Budget Num
    hrs

    Table2
    EMP ID
    PayendData

  4. #4
    Join Date
    Jun 2006
    Posts
    22
    tonkuma - I've tried several queries using TABLE1.EmpID = TABLE2.EmpID. I've tried using a table subquery, a subquery for the count(EmpID) column and INNER JOIN. The table subquery totally tanked but the column subquery worked with incorrect results. If you would point me in the correct direction, I would be more than happy to post the SQL if my attempt is unsuccessful. I don't mind posting one of my previous failures if need be just pick one.
    I believe we are using version 9 and I'm using MS Query to create then import to an Excel spreadsheet. I can also access it through the IBM mainframe (z/OS or OS/390).

    shrivatsa:
    TABLE1
    EmpID - primary key
    EmpStatus - not displaying this but filtering based on it.

    TABLE2
    EmpID - NOT primary key (no key)
    PayEndDate - filtering on this (>1/1/year)
    BudgetNum - filtering on this (8 budget numbers)
    Hours - summing
    ExpendType - filtering on this (eliminating certain types)

  5. #5
    Join Date
    May 2006
    Posts
    20

    Joins

    Hi

    Table 1
    Code:
    select * from shb_temp_table1;
    
         EMPID S
    ---------- -
             1 A
             2 A
             3 A
    Code:
      1* select * from shb_temp_table2
    
         EMPID PAYENDDAT  BUDGETNUM      HOURS EXPENDTYPE
    ---------- --------- ---------- ---------- --------------------
             1 06-JAN-10      12312         12 A
             1 05-JAN-10      12311         12 A
             2 05-JAN-10      12314         11 A
             2 06-JAN-10      12315         12 A
    
    Elapsed: 00:00:00.06
    Code:
    select a.empid
    ,b.payenddate
    ,sum(b.hours)
    ,count(*)
    from shb_temp_table1 a, shb_temp_table2 b
    where a.empid in (select empid from shb_temp_table2
    group by empid)
    group by payenddate,b.hours, a.empid
    having count(*)>1;
    
    Code:
    Output
    
    
         EMPID PAYENDDAT SUM(B.HOURS)   COUNT(*)
    ---------- --------- ------------ ----------
             1 06-JAN-10           24          2
             2 06-JAN-10           24          2
    Hope this will give you the desired results

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, I know your queries must produce undesired results,
    it would save much of my time of guessing and typing and may save some of furthermore dialogue between us,
    if you supplied some of your trial queries and the (incorrect) sample output.

    Anyway, this may give you some hint.
    Code:
    SELECT t1.EmpID
         , PayEndDate
         , BudgetNum
         , SUM(Hours)      AS hours_sum
         , COUNT(t2.EmpID) AS count_t2_rows
      FROM TABLE1 t1
         , TABLE2 t2
     WHERE t1.EmpID = t2.EmpID
       AND (predicates for EmpStatus)
       AND (predicates for ExpendType)
       AND PayEndDate > CURRENT_DATE - (DAYOFYEAR(CURRENT_DATE) - 1) DAYs
       AND BudgetNum IN (...)
     GROUP BY
           t1.EmpID
         , PayEndDate
         , BudgetNum
    ;

  7. #7
    Join Date
    Jun 2006
    Posts
    22

    Question

    Is it possible to get the same results without displaying EmpID just using Count(EmpID)? If I display EmpID the counts comes out correct, if I remove it the group by doesn't work correctly. I've tried several things but most recently I tried tonkuma's suggestion and removed the t1.EmpID from the select clause. I go from a count of 129 to 320 when I do this.

    I would like to display the following columns:

    PayEndDate
    BudgetNum
    Sum(hours)
    Count(EmpID)

    Thanks for all of your help.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about removing t1.EmpID from SELECT clause and GROUP BY clause in my example?
    Like this....
    Code:
    SELECT
           PayEndDate
         , BudgetNum
         , SUM(Hours)      AS hours_sum
         , COUNT(t2.EmpID) AS count_t2_rows
      FROM TABLE1 t1
         , TABLE2 t2
     WHERE t1.EmpID = t2.EmpID
       AND (predicates for EmpStatus)
       AND (predicates for ExpendType)
       AND PayEndDate > CURRENT_DATE - (DAYOFYEAR(CURRENT_DATE) - 1) DAYs
       AND BudgetNum IN (...)
     GROUP BY
           PayEndDate
         , BudgetNum
    ;
    or, change "COUNT(t2.EmpID)" to "COUNT(t1.EmpID)" or "COUNT(DISTINCT t2.EmpID)" in the new example.
    Last edited by tonkuma; 01-07-10 at 08:01.

  9. #9
    Join Date
    Jun 2006
    Posts
    22

    Thumbs up

    Count(DISTINCT t1.EmpID) dit it!! Thanks!!

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I would appreciate it if you would keep in mind...
    .......
    it would save much of my time of guessing and typing and may save some of furthermore dialogue between us,
    if you supplied some of your trial queries and the (incorrect) sample output.

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
  •