Table Join

Nebraska_God · #1 (**permalink**) 01-06-10, 03:40

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.

tonkuma · #2 (**permalink**) 01-06-10, 04:01

Please show your entire query(SELECT statement) and write DB2 version/release and platform(OS) on your DB2 server is running.

shrivatsa · #3 (**permalink**) 01-06-10, 04:03

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

Nebraska_God · #4 (**permalink**) 01-06-10, 04:21

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)

shrivatsa · #5 (**permalink**) 01-06-10, 05:44

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

tonkuma · #6 (**permalink**) 01-06-10, 06:14

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
;

Nebraska_God · #7 (**permalink**) 01-07-10, 04:32

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.

tonkuma · #8 (**permalink**) 01-07-10, 06:57

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.

Nebraska_God · #9 (**permalink**) 01-07-10, 21:23

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

tonkuma · #10 (**permalink**) 01-08-10, 15:33

I would appreciate it if you would keep in mind...

Quote:

.......
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.

Internet Services

Web Development

Digital Marketing

Consumer Tech