When taking count on group function that is if i write count(column)=1 i not getting the output
select NMAP+' '+NLOT ,NREF,IIREF,NNAM,NAD1,NAD2,IISQFT,IIYRBL,NRBED,NRBATH from OR_Baker_09_Main_02 nolock inner join OR_Baker_09_Main_03 on NREF=IIREF where NMAP+' '+NLOT in (select NMAP+' '+NLOT from OR_Baker_09_Main_02 group by NMAP+' '+NLOT having COUNT(NMAP+' '+NLOT)=1)order by (NMAP+' '+NLOT)
It looks like you only want to select records that are also in OR_Baker_09_Main_02. In which case try replacing the IN subquery with this: select distinct NMAP + ' ' + NLOT from OR_Baker_09_Main_02 PS: Check that your subquery actually works. I seem to remember that, if there's an error in an IN subquery, the error is not raised but instead the IN clause just doesn't cause anything to qualify in the outer query.