Hey Ken, I'd recommed, first of all that you use a left outer join instead of an inner join... that's why you're excluding non-matching records. This should get you all employee records and anything in the SASCalendar table that matches the employeeID:
FROM employee LEFT OUTER JOIN SASCalendar ON employee.EmpID = SASCalendar.EmployeeID Also I notice you're selecting EmpID and EmployeeID both... is there a reason for the duplicate data? AND, finally, I'd include the following: order by eployeeID, yearMonth, leaveType I'm curious, are you using QofQ to get your stats, or are you looping the records to get your summaries for your various leaveTypes? Laterz, J -- Continuum Media Group LLC Burnsville, MN 55337 http://www.web-relevant.com On Sat, 25 Dec 2004 20:44:23 -0500, Ken <[EMAIL PROTECTED]> wrote: > Hi. I am having problems doing a table join in the following scenario: > Table 1: Employee Info: EmployeeID, FirstName, Email > Table 2: Leave info submitted by employees: EmployeeID, FirstName, > LastName, DateEntered, LeaveType, LeaveStatus > > Each record in Table 2 represents on day of leave. So if i am taking a > 10 day vacation, there will be 10 records for that vacation in Table 2 > for me. > > I need to display a report that shows all leaves (vacation, sick days) > taken by all employees in current month. The display format needs to > be like this: > > EmployeeName | Email | Leave summary for Current Month > Vacation | Sick > > Tom [EMAIL PROTECTED] 5 6 > > John [EMAIL PROTECTED] 4 3 > > I know it needs a table join but it doesn't seem to work. The join is > showing the records that are common to both tables. It is excluding > the unique records. I am using this select statement: > SELECT EmpID, FirstName, LastName, Email, LeaveLogID, EmployeeID, Leavetype > FROM (SASCalendar INNER JOIN employee ON employee.EmpID = > SASCalendar.EmployeeID) > where ApproverID=10005 AND (YearMonth like '%12' and YearMonth like '2004%') > > Please help urgently. > > - Kenn ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188784 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
