Limiting rows returned from count of records in a subquery.
Posted on 2014-03-26
I am trying to nut out a query which returns the COUNT of rows in a history table where a resultcode is 'NOT' and that COUNT is more than 2.
I have been sweeping the Net to find a clue, alas without luck.
Select KEY1,Company, (select COUNT(resultcode) from C1)
inner join CONTACT2 on Contact1.ACCOUNTNO = CONTACT2.ACCOUNTNO
where Exists (select key1,COUNT(RESULTCODE)
from CONTACT1 c1
inner join CONTACT2 c2 on C1.ACCOUNTNO = C2.ACCOUNTNO
left join CONTHIST CH on CH.ACCOUNTNO = C1.ACCOUNTNO
CH.RESULTCODE = 'SHD'
GROUP BY C1.KEY1)
I get this error "Invalid object name 'C1'"
All help is achknowledged.