Using COUNT() in WHERE Clause

Question

I have the following SQL statement:

SELECT     C.LINKED_TABLE_ID AS CLIENT_DIWOR, I.STATUS AS AML_STATUS, dbo.CLIENT_MASTER.CLIENTCODE
FROM         AML_INFORMATION AS I INNER JOIN
                dbo.COMM_ENTRY AS C ON C.NAME_ID = I.CONTACT_ID AND C.TABLE_ID = 'C' AND C.PRIMARY_FLAG = 'Y' INNER JOIN
                dbo.CLIENT_MASTER ON C.LINKED_TABLE_ID = dbo.CLIENT_MASTER.DIWOR
WHERE I.CONTACT_ID = 234
    AND I.[STATUS] = 'CC'
    AND (CLIENT_MASTER.DIWOR = I.CONTACT_ID)
    AND (CLIENT_MASTER.POSTING_STATUS <> '')
    AND ((SELECT COUNT(CONTACT_ID) FROM AML_ID_DOCUMENT GROUP BY CONTACT_ID HAVING CONTACT_ID = 234) >1)

If I run this it returns 0 records, however if I remove the last AND statement AND ((SELECT COUNT(CONTACT_ID) FROM AML_ID_DOCUMENT GROUP BY CONTACT_ID HAVING CONTACT_ID = 234) >1) it returns the records I would expect.

Is it possible to use a COUNT() in this way? Incidentally, the COUNT() in this example returns 2 records and if I include it in the SELECT statement I also get 0 records returned.

Can anyone point me in the right direction?

Thanks in advance.

Answer 1

Your showed Script should never return anything ... your having part could look this way

   and (Select Count(*) from AML_ID_DOCUMENT  where CONTACT_ID =I.CONTACT_ID)>1

though ist should work as it is...