Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Joins

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-10-25 : 14:55:51

Greetings all,

I'm having a little problem with the join between two tables.

Table A: contains employee data, one row per employee, multiple columns of data concerning the employee.

Table B: contains company data by employee. Only 5 columns in size, but there can be mulitple rows of data on a single employee, each row would be a single period of time. The rows are not combined or linked in any way.

Once the join has been established I can make some basic calls between the tables and get the results I need. My problem is when I query the tables for some SUM totals that everything goes crazy.

As an example; Employee 001, has several columns in Table A that I would like to total. The results would be.. 3 Occurrences, 24.0 Hours, and 3 Points. When I have the query at the same time provide me a total of Hours in Table B where there are 15 rows of data on the employee in question, I get the correct amount, but my amounts from Table A are now 45 Occurrences, 360 Hours, and 45 Points.

I seem to be getting an echo count from Table A for each row on the employee in Table B.

I've tried all the Joins, FULL, Right, Left without any success. Started reading about Unions in one of my SQL books and was wondering if I'm on the right path, or way off.

Thanks as always.

GC

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-25 : 15:23:48

Can you give us some sample data and table structure mimicing what your using?

-Jon
Should still be a "Starting Member"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-25 : 15:26:42

quote:
Table A: contains employee data, one row per employee, multiple columns of data concerning the employee.

quote:
Employee 001, has several columns in Table A that I would like to total. The results would be.. 3 Occurrences, 24.0 Hours, and 3 Points.

I think that the information you gave doesn't reflect the way your database looks.
Most likely you have multiple occurences of the employee in Table A.

The following advise will not help you right now, but if you manage to do it, it will save you lots of problems down the road.
If Table A has only one row per employee, put a PK or Unique Index/Constraint on the column that identifies the employee
Put a Foreign Key constraint between TableA and TableB on that column.

If you want more help plz post the table definitions and the query you got so far.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-10-29 : 13:11:11

Gary, can you restrict the rows returned in the join by putting additional criteria in the JOIN clause? For example:
SELECT ... FROM A LEFT JOIN B on A.ID = B.ID and B.StartDate >= '1/1/04' and B.EndDate <= '3/30/04' ...

See my article on Additional Criteria in the JOIN Clause for more explanation and samples.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-12-08 : 18:30:06

AjarnMark and All,

Sorry for the late response to this, things have finally slowed down so that I can revisit this.

The main Table called "CTwentyOne" as described above contains one row for each employee with multiple columns of data. Here is an example of one row for an employee.

EmployeeNumber CNT Total HRS Total PTS Total
322263 1 25.8 0

(There is more data then this, but I have kept it short to fit the page)

The other table that contains the additional data I want to use is called "AuoTAFMLAData". Here is the data in this table for the same employee.

CompanyCode EmployeeID PayDate PayCode PayHours
AA 00322263 07/21/04 IUF 7
AA 00322263 07/22/04 IUF 7
AA 00322263 07/23/04 IUF 7
AA 00322263 07/19/04 IUF 7
AA 00322263 07/20/04 IUF 7
AA 00322263 07/14/04 IUF 7
AA 00322263 07/15/04 IUF 7
AA 00322263 07/16/04 IUF 7
AA 00322263 07/12/04 IUF 7
AA 00322263 07/13/04 IUF 7
AA 00322263 07/07/04 IUF 7
AA 00322263 07/08/04 IUF 7
AA 00322263 07/09/04 IUF 7
AA 00322263 07/05/04 IUF 7

(There is actually 143 rows for this employee)

Here is the SQL query I am using.

SELECT losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.CTwentyOne.EmployeeStatus,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.CompanySeniortyDate,
Case
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 365 and DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())<= 1460 Then '001 - 004'
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 1461 and DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())<= 3285 Then '005 - 009'
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 3286 and DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())<= 5475 Then '010 - 015'
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 5476 and DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())<= 6935 Then '016 - 019'
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 6936 and DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())<= 10585 Then '020 - 029'
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 10586 and DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())<= 14235 Then '030 - 039'
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 14236 and DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())<= 17885 Then '040 - 049'
When DateDiff(Day,losttime.CTwentyOne.CompanySeniortyDate, Getdate())>= 17886 Then '050 ++'
Else '000'
End as 'Sen Years',
CASE
WHEN (Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Profit_Centers_Table.StationsReportingToCARGO = 'CARGO' and losttime.Branches_Table.CARGOCATEGORIES = 'CGO - Field') THEN 'CARGO'
WHEN (Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Profit_Centers_Table.StationsReportingToRES = 'RES' and losttime.Branches_Table.BranchCategorySmall = 'RES') THEN 'RES'
WHEN (losttime.Branches_Table.BranchCategorySmall = 'MNTC' and losttime.Branches_Table.MAINTENANCECATEGORIES = 'MNTC - BASE'and losttime.CTwentyOne.StationCode = '1020') THEN 'M&E AFW BASE'
WHEN (losttime.Branches_Table.BranchCategorySmall = 'MNTC' and losttime.Branches_Table.MAINTENANCECATEGORIES = 'MNTC - BASE'and losttime.CTwentyOne.StationCode = '1355') THEN 'M&E MCIE BASE'
WHEN (losttime.Branches_Table.BranchCategorySmall = 'MNTC' and losttime.Branches_Table.MAINTENANCECATEGORIES = 'MNTC - BASE'and losttime.CTwentyOne.StationCode = '200') THEN 'M&E TULE BASE'
WHEN (Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Branches_Table.BranchCategorySmall = 'MNTC' and losttime.Branches_Table.MAINTENANCECATEGORIES = 'MNTC - LINE') THEN 'M&E LINE'
WHEN (Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Branches_Table.BranchCategorySmall = 'MNTC' and losttime.Branches_Table.MAINTENANCECATEGORIES = 'MNTC - QA') THEN 'M&E QA'
WHEN (Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Branches_Table.BranchCategorySmall = 'MNTC' and losttime.Branches_Table.MAINTENANCECATEGORIES = 'MNTC - STORES') THEN 'M&E STORES'
WHEN (losttime.Profit_Centers_Table.AirportCode NOT IN ('ALB', 'ROC')and Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Branches_Table.BranchCategorySmall = 'PSVC')and (losttime.Profit_Centers_Table.StationsReportingToCARGO <> 'CARGO' or losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field') or (losttime.Profit_Centers_Table.AirportCode = 'SJU' and losttime.Branches_Table.BranchCategorySmall = 'RES')THEN 'AGENTS'
WHEN (losttime.Profit_Centers_Table.AirportCode NOT IN ('ALB', 'ROC')and Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Branches_Table.BranchCategorySmall = 'RSVC')and (losttime.Profit_Centers_Table.StationsReportingToCARGO <> 'CARGO' or losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field')THEN 'CLERKS'
--WHEN (Losttime.Profit_Centers_Table.CountryCode IN ('US','CA','USVI')and losttime.Branches_Table.BRANCHCATEGORYSMALL = 'PREM SVC')THEN 'PREM SVC'
--WHEN (losttime.Branches_Table.BranchCategorySmall = 'OP&P') THEN 'OP&P'
ELSE 'OTHER'
END AS Department,
losttime.CTwentyOne.SickHours_Jan_01 as 'SK_Beg',
losttime.CTwentyOne.SickHours_Current as 'SK_Cur',
CASE
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 1201 THEN '1200++'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 1101 and losttime.CTwentyOne.SickHours_Jan_01 <= 1200 THEN '1101 - 1200'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 1001 and losttime.CTwentyOne.SickHours_Jan_01 <= 1100 THEN '1001 - 1100'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 901 and losttime.CTwentyOne.SickHours_Jan_01 <= 1000 THEN '0901 - 1000'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 801 and losttime.CTwentyOne.SickHours_Jan_01 <= 900 THEN '0801 - 0900'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 701 and losttime.CTwentyOne.SickHours_Jan_01 <= 800 THEN '0701 - 0800'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 601 and losttime.CTwentyOne.SickHours_Jan_01 <= 700 THEN '0601 - 0700'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 501 and losttime.CTwentyOne.SickHours_Jan_01 <= 600 THEN '0501 - 0600'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 401 and losttime.CTwentyOne.SickHours_Jan_01 <= 500 THEN '0401 - 0500'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 301 and losttime.CTwentyOne.SickHours_Jan_01 <= 400 THEN '0301 - 0400'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 201 and losttime.CTwentyOne.SickHours_Jan_01 <= 300 THEN '0201 - 0300'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 161 and losttime.CTwentyOne.SickHours_Jan_01 <= 200 THEN '0161 - 0200'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 121 and losttime.CTwentyOne.SickHours_Jan_01 <= 160 THEN '0121 - 0160'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 81 and losttime.CTwentyOne.SickHours_Jan_01 <= 120 THEN '0081 - 0120'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 41 and losttime.CTwentyOne.SickHours_Jan_01 <= 80 THEN '0041 - 0080'
WHEN losttime.CTwentyOne.SickHours_Jan_01 >= 1 and losttime.CTwentyOne.SickHours_Jan_01 <= 40 THEN '0001 - 0040'
ELSE '000'
END AS 'Sick Bank Hours Group Begin',
CASE
WHEN losttime.CTwentyOne.SickHours_Current >= 1201 THEN '1200++'
WHEN losttime.CTwentyOne.SickHours_Current >= 1101 and losttime.CTwentyOne.SickHours_Current <= 1200 THEN '1101 - 1200'
WHEN losttime.CTwentyOne.SickHours_Current >= 1001 and losttime.CTwentyOne.SickHours_Current <= 1100 THEN '1001 - 1100'
WHEN losttime.CTwentyOne.SickHours_Current >= 901 and losttime.CTwentyOne.SickHours_Current <= 1000 THEN '0901 - 1000'
WHEN losttime.CTwentyOne.SickHours_Current >= 801 and losttime.CTwentyOne.SickHours_Current <= 900 THEN '0801 - 0900'
WHEN losttime.CTwentyOne.SickHours_Current >= 701 and losttime.CTwentyOne.SickHours_Current <= 800 THEN '0701 - 0800'
WHEN losttime.CTwentyOne.SickHours_Current >= 601 and losttime.CTwentyOne.SickHours_Current <= 700 THEN '0601 - 0700'
WHEN losttime.CTwentyOne.SickHours_Current >= 501 and losttime.CTwentyOne.SickHours_Current <= 600 THEN '0501 - 0600'
WHEN losttime.CTwentyOne.SickHours_Current >= 401 and losttime.CTwentyOne.SickHours_Current <= 500 THEN '0401 - 0500'
WHEN losttime.CTwentyOne.SickHours_Current >= 301 and losttime.CTwentyOne.SickHours_Current <= 400 THEN '0301 - 0400'
WHEN losttime.CTwentyOne.SickHours_Current >= 201 and losttime.CTwentyOne.SickHours_Current <= 300 THEN '0201 - 0300'
WHEN losttime.CTwentyOne.SickHours_Current >= 161 and losttime.CTwentyOne.SickHours_Current <= 200 THEN '0161 - 0200'
WHEN losttime.CTwentyOne.SickHours_Current >= 121 and losttime.CTwentyOne.SickHours_Current <= 160 THEN '0121 - 0160'
WHEN losttime.CTwentyOne.SickHours_Current >= 81 and losttime.CTwentyOne.SickHours_Current <= 120 THEN '0081 - 0120'
WHEN losttime.CTwentyOne.SickHours_Current >= 41 and losttime.CTwentyOne.SickHours_Current <= 80 THEN '0041 - 0080'
WHEN losttime.CTwentyOne.SickHours_Current >= 1 and losttime.CTwentyOne.SickHours_Current <= 40 THEN '0001 - 0040'
ELSE '000'
END AS 'Sick Bank Hours Group End',

CASE
WHEN (losttime.CTwentyOne.CorrectiveAction NOT IN ('AIC', '1STADV', '2NDADV', 'TRMWRN')) and (losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365) THEN 'Dr Slip'
WHEN (losttime.CTwentyOne.CorrectiveAction = 'AIC')THEN 'AIC'
WHEN (losttime.CTwentyOne.CorrectiveAction = '1STADV')THEN '1STADV'
WHEN (losttime.CTwentyOne.CorrectiveAction = '2NDADV')THEN '2NDADV'
WHEN (losttime.CTwentyOne.CorrectiveAction = 'TRMWRN')THEN 'TRMWRN'
WHEN (SUM(losttime.CTwentyOne.SKL_Count + losttime.CTwentyOne.ID_Count + losttime.CTwentyOne.IU_Count + losttime.CTwentyOne.NC_Count + losttime.CTwentyOne.PO_Count + losttime.CTwentyOne.RL_Count + losttime.CTwentyOne.SK_Count + losttime.CTwentyOne.SKI_Count + losttime.CTwentyOne.SKP_Count + losttime.CTwentyOne.SKQ_Count + losttime.CTwentyOne.SKU_Count + losttime.CTwentyOne.UA_Count) >= 1.0) and (SUM(losttime.CTwentyOne.SKL_Count + losttime.CTwentyOne.ID_Count + losttime.CTwentyOne.IU_Count + losttime.CTwentyOne.NC_Count + losttime.CTwentyOne.PO_Count + losttime.CTwentyOne.RL_Count + losttime.CTwentyOne.SK_Count + losttime.CTwentyOne.SKI_Count + losttime.CTwentyOne.SKP_Count + losttime.CTwentyOne.SKQ_Count + losttime.CTwentyOne.SKU_Count + losttime.CTwentyOne.UA_Count) <= 2.0) THEN '2 Occ or Less'
WHEN (SUM(losttime.CTwentyOne.SKL_Count + losttime.CTwentyOne.ID_Count + losttime.CTwentyOne.IU_Count + losttime.CTwentyOne.NC_Count + losttime.CTwentyOne.PO_Count + losttime.CTwentyOne.RL_Count + losttime.CTwentyOne.SK_Count + losttime.CTwentyOne.SKI_Count + losttime.CTwentyOne.SKP_Count + losttime.CTwentyOne.SKQ_Count + losttime.CTwentyOne.SKU_Count + losttime.CTwentyOne.UA_Count) >= 3.0) THEN '3 Occ or More'
WHEN (SUM(losttime.CTwentyOne.SKL_Count + losttime.CTwentyOne.ID_Count + losttime.CTwentyOne.IU_Count + losttime.CTwentyOne.NC_Count + losttime.CTwentyOne.PO_Count + losttime.CTwentyOne.RL_Count + losttime.CTwentyOne.SK_Count + losttime.CTwentyOne.SKI_Count + losttime.CTwentyOne.SKP_Count + losttime.CTwentyOne.SKQ_Count + losttime.CTwentyOne.SKU_Count + losttime.CTwentyOne.UA_Count) = 0) and (SUM(losttime.AutoTAFMLAData.PayHours) > 1) THEN 'P.A. with FMLA'
ELSE 'P.A.'
END AS 'LostTimeCategoryGroup',
SUM(losttime.CTwentyOne.SKL_Count
+ losttime.CTwentyOne.ID_Count
+ losttime.CTwentyOne.IU_Count
+ losttime.CTwentyOne.NC_Count
+ losttime.CTwentyOne.PO_Count
+ losttime.CTwentyOne.RL_Count
+ losttime.CTwentyOne.SK_Count
+ losttime.CTwentyOne.SKI_Count
+ losttime.CTwentyOne.SKP_Count
+ losttime.CTwentyOne.SKQ_Count
+ losttime.CTwentyOne.SKU_Count
+ losttime.CTwentyOne.UA_Count) AS TotalCount,
SUM(losttime.CTwentyOne.SKL_Hours
+ losttime.CTwentyOne.ID_Hours
+ losttime.CTwentyOne.IU_Hours
+ losttime.CTwentyOne.NC_Hours
+ losttime.CTwentyOne.PO_Hours
+ losttime.CTwentyOne.RL_Hours
+ losttime.CTwentyOne.SK_Hours
+ losttime.CTwentyOne.SKI_Hours
+ losttime.CTwentyOne.SKP_Hours
+ losttime.CTwentyOne.SKQ_Hours
+ losttime.CTwentyOne.SKU_Hours
+ losttime.CTwentyOne.UA_Hours) AS TotalHours,
SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) AS TotalPoints,
CASE
WHEN (losttime.AutoTAFMLAData.EmployeeID = losttime.CTwentyOne.EmployeeNumber) Then (SUM(losttime.AutoTAFMLAData.PayHours))
ELSE 0
END AS 'TotalFMLAHours',
CASE
When (losttime.RES_PT_Non_Extendable.EMPID = losttime.CTwentyOne.EmployeeNumber) THEN 'PTN'
ELSE 'NON-PTN'
END AS 'RES PTN'
FROM losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
JOIN losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
FULL JOIN RES_PT_Non_Extendable ON losttime.CTwentyOne.EmployeeNumber = losttime.RES_PT_Non_Extendable.EMPID
FULL OUTER JOIN losttime.AutoTAFMLAData ON losttime.CTwentyOne.EmployeeNumber = losttime.AutoTAFMLAData.EmployeeID
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus <> 'Off Payroll'
GROUP BY losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.AutoTAFMLAData.EmployeeID,
losttime.CTwentyOne.EmployeeStatus,
losttime.Profit_Centers_Table.CountryCode,
losttime.CTwentyOne.StationCode,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.CompanySeniortyDate,
losttime.CTwentyOne.SickHours_Jan_01,
losttime.CTwentyOne.SickHours_Current,
losttime.CTwentyOne.CorrectiveAction,
losttime.Branches_Table.BRANCHCATEGORYSMALL,
losttime.Profit_Centers_Table.STATIONSREPORTINGTOPSVC,
losttime.Profit_Centers_Table.StationsReportingToCARGO,
losttime.Branches_Table.CARGOCATEGORIES,
losttime.Profit_Centers_Table.STATIONSREPORTINGTORSVC,
losttime.Profit_Centers_Table.StationsReportingToRES,
losttime.Branches_Table.MAINTENANCECATEGORIES,
losttime.CTwentyOne.DrSlipStartDate,
RES_PT_Non_Extendable.EMPID,
losttime.RES_PT_Non_Extendable.PTN
ORDER BY losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode

Here is the response I get for the employee.

ID Status Sta BRA Co. Sen Co Years Dept SK Beg. SK Cur. SK Bank Grp Beg. SK Bank Grp Cur. L.T. Cat. L.T. Count L.T. Hours L.T. Points FMLA Hours RES - PTN

322263 ACTIVE TUL MB 7881 10/09/91 010 - 015 M&E TULE BASE 234.0 193.8 0201 - 0300 0161 - 0200 3 Occ or More 143.0 3,689.4 0.0 1,027.0 NON-PTN

Everything works fine with the exception of the Totals for "L.T. Count and L.T. Hours"

The data in the first table reads a Total Count of 1 and Totals Hours of 25.8.

The response from the Query shows Total Count of 143 and Totals Hours of 3,689.4. It is obviously continually adding the Count and Hours for each row in the other table. 25.8 hours multiplied by 143 equals 3,689.4.

The FMLA Hours are correct at 1,027.

I'm sure it has to do with the way I'm joining the two tables, or it's my coding.

GC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources