Thanks Jared. I am simply looping through the records using <cfoutput query="abc">
Also, i just tried your select statement and i am still getting the exact same recordset. Here's what I am doing now: SELECT FirstName, LastName, Email, LeaveLogID, EmployeeID, Leavetype FROM employee LEFT OUTER JOIN SASCalendar ON employee.EmpID = SASCalendar.EmployeeID where ApproverID=10005 AND (YearMonth like '%12' and YearMonth like '2004%') order by employeeID, yearMonth, leaveType Any ideas? should i be using Q of Q? If so, please guide. Thanks, Ken On Sat, 25 Dec 2004 20:22:26 -0600, Jared Rypka-Hauer - CMG, LLC <[EMAIL PROTECTED]> wrote: > 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 - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188787 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
