CODEWITHSUNDEEP
sqlms-access
Sally
Sally

Reputation: 77

SQL Select rows having count > 1 and a row not exists

I am trying to write an sql in MS Access where:

I have a table having columns(id,Name,code)

I need to select all the records which have same Name occuring more than once where there does not exist among the rows a row where code IS NULL

I have tried this but it doesn't work

SELECT Name
FROM   TableName
GROUP  BY Name
HAVING NOT EXISTS ( Select Name FROM TableName Where code IS NULL Group By Name Having COUNT(*) > 1) ;

Upvotes: 1

Views: 463

Answers (1)

juergen d
juergen d

Reputation: 204904

SELECT   Name
FROM     TableName
GROUP BY Name
HAVING   sum(IIF(code is null OR code = 0, 1, 0)) = 0
   AND   COUNT(*) > 1

Upvotes: 1

Related Questions