Use GROUP BY and grouped output. The code may need some tweaking as your
previous posts didn't have all the info (or contradicting info).
<cfquery datasource="#request.dsn#" name="qLeave">
SELECT e.FirstName, e.LastName, e.Email, e.EmployeeID, s.Leavetype,
COUNT(*) AS totalDays
FROM employee e LEFT OUTER JOIN SASCalendar s ON e.EmployeeID =
s.EmployeeID
WHERE s.ApproverID = <cfqueryparam cfsqltype="cf_sql_integer"
value="10005">
AND s.YearMonth like <cfqueryparam cfsqltype="cf_sql_varchar"
value="2004%12">
GROUP BY e.FirstName, e.LastName, e.Email, e.EmployeeID, s.Leavetype
ORDER BY e.EmployeeID, s.Leavetype
</cfquery>
<cfoutput query="qLeave" group="EmployeeID">
#qLeave.LastName# #qLeave.FirstName#, #qLeave.Email# :
<cfoutput>
#qLeave.Leavetype# (#qLeave.totalDays#)
</cfoutput>
<br>
</cfoutput>
Pascal
> -----Original Message-----
> From: Ken [mailto:[EMAIL PROTECTED]
> Sent: 26 December 2004 04:46
> To: CF-Talk
> Subject: Re: Table join issue
>
> That's right. But employee name etc is in employee table and the leave
> data is stored in a seperate table. Each day of leave in that table is
> one record. So i need recordcount for each leavetype for that
> employee.
> Hope that answers your question. Any clues?
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188795
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
