asphaltninja
asked on
SQL Join using temp table that also has count totals, am I doing this right?
So I have one table that stores information about employees, and another table that stores information about errors associated with each user.
What I need to do is retrieve a dataset that holds the user's information in the first 5 fields, and then add an 'error' column at the end that's a sum of the user's errors from the Errors table. The schema is (roughly) as follows:
Employee table:
SSN varchar (primary key)
plnCode varchar,
FirstName varchar,
LastName varchar,
Cont int,
Comp int
Errors Table:
ErrorCode int(Primary key),
SSN varchar(foriegn key)
isCritical bit
Here's what I have so far to retieeve the data:
I get an "An aggregate may not appear in the set list of an UPDATE statement." error when I try to execute. What am I doing wrong?
What I need to do is retrieve a dataset that holds the user's information in the first 5 fields, and then add an 'error' column at the end that's a sum of the user's errors from the Errors table. The schema is (roughly) as follows:
Employee table:
SSN varchar (primary key)
plnCode varchar,
FirstName varchar,
LastName varchar,
Cont int,
Comp int
Errors Table:
ErrorCode int(Primary key),
SSN varchar(foriegn key)
isCritical bit
Here's what I have so far to retieeve the data:
ALTER PROCEDURE getContCollecinfo
@plnCode varchar(10)
AS
DECLARE @ssn varchar(12)
SELECT a.ssn, a.lname,a.fname, a.cont, a.comp
into #tempa from cont_collec_payroll_info a
JOIN contMessages b ON
a.SSN = b.SSN
JOIN cont_Collec_MessageType c ON
b.messCode = c.messCode
where a.plnCode=@plnCode and c.IsError = 1
alter table #tempa
add Errors int
update #tempa
SET Errors = count(d.isError) from cont_collec_messagetype d
join contmessages e on
d.messCode = e.messCode
join #tempa f on
e.ssn = f.ssn
where d.isError = 1 and f.ssn = e.ssn
select * from #tempa
drop table #tempa
I get an "An aggregate may not appear in the set list of an UPDATE statement." error when I try to execute. What am I doing wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked perfectly, thanks!
Open in new window