... AND (SELECT COUNT(*) FROM dbo.meetproc mp WHERE dbo.meetings.meetingnumber = mp.meetingnumber AND (mp.coperid = @tiSurgeonID) and (mp.procid=@tiProcID)) = 1 ...>Once again, this query performs exactly as written....but i failed to properly understand a requirement. That is that when I select the data from meetings & meetproc, I only want the meetings that have only one child record in the meetproc table where the meetproc.procid=@tiProcID. Meetproc is a child table to meetings. Most the time, there is only one child record for each meeting record, BUT sometimes there are more. I want to exclude the ones that have more than one child record in meetproc, even if that meetproc record has the right @tiProcID.
>select avg(det.ortime) as AvgORMinutes, > (Select isnull(cleanup,0) from coProc where coProc.ProcID=det.procid) as Cleanup, > (Select isnull(setup,0) from coProc where coProc.ProcID=det.procid) as Setup > from (select top 20 > dbo.meetings.enteror, dbo.meetings.exitor, > datediff(mi,enteror,exitor) as ORTime,meetproc.procid > FROM dbo.meetings INNER JOIN > dbo.meetproc ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber > WHERE (dbo.meetproc.coperid = @tiSurgeonID) AND (dbo.meetings.iscompleted = 1) > and (dbo.meetproc.procid=@tiProcID) > ORDER by endtime desc )det >group by det.procid > >