[DarenR]

how can i get the count of one table in a multi table query.

SELECT     *, ((count(*)from t_User_Trace group by t_user_trace.txt_IP_Address) as trace_count )
FROM         t_Employee 
INNER JOIN t_Vendor_Employee WITH (NOLOCK) ON t_Employee.employee_ID = t_Vendor_Employee.employee_ID 
INNER JOIN t_User WITH (NOLOCK) ON t_Employee.user_id = t_User.user_ID 
INNER JOIN t_User_Group ON t_User.user_group_ID = t_User_Group.user_group_ID
inner join t_User_Trace on t_User.txt_username = t_User_Trace.txt_username
WHERE     (t_Vendor_Employee.vendor_employee_id = 642391) 
--(select  COUNT(*)as _count  from t_User_Trace

i need to count the records in t_User_Trace and I cnat remeber how to do it. But at the same time I also need to return everything from all the above tables.

This post has been edited by macosxnerd101: 31 July 2012 - 01:40 PM
Reason for edit:: Please use a descriptive titel

[macosxnerd101]

DarenR, you have more than 850 posts. Please use a descriptive title. "I have a question" and "I forgot how to" are not descriptive titles.

[DarenR]

I was just about to change that lol but you beat me to it-- thanks

[e_i_pi]

You're nearly there with the query, but you need to do your aggregating within a subquery, so that you can GROUP within that subquery, thus avoiding SELECT DISTINCT or GROUP BY in your outer query...

SELECT
	*,
	UserTraceAggregate.trace_count
FROM t_Employee 
INNER JOIN t_Vendor_Employee WITH (NOLOCK) ON t_Employee.employee_ID = t_Vendor_Employee.employee_ID 
INNER JOIN t_User WITH (NOLOCK) ON t_Employee.user_id = t_User.user_ID 
INNER JOIN t_User_Group ON t_User.user_group_ID = t_User_Group.user_group_ID
INNER JOIN (
	SELECT
		COUNT(*) AS trace_count,
		txt_IP_Address,
		txt_username
	FROM t_User_Trace
	GROUP BY
		txt_IP_Address,
		txt_username
) AS UserTraceAggregate ON t_User.txt_username = t_User_Trace.txt_username
WHERE (t_Vendor_Employee.vendor_employee_id = 642391)