VBA SQL - Filter Group By Over entire Group

[pwag]

Good Morning Pro's,

I am trying to write a script that selects a group based on the criteria within that group. I'm looking to pull in only records that meet that criteria.

SELECT ColumnA
FROM Table
GROUP BY ColumnA
HAVING ColumnA = 'A'

With the GROUP BY, SQL will pull in my GROUP even if there are two records with 'A' or 'B', rightfully so. I'm trying to only select the group when all Columns meet the criteria of 'A'. In otherwards, if there is a 'B' in the Group, I'd like to NOT SELECT it.

Any creative solutions are welcome. Thanks guys!

P

[NeoPa]

Try something like this :

SELECT Min([ColumnA]) AS [MinColA]
FROM   [Table]
HAVING (Max([ColumnA])='A')

[pwag]

Hi NeoPa,

Thanks! Exactly what I needed. I used this(a variation of your example):

HAVING (Count(ColumnA))=(SELECT Count(ColumnA) FROM Table WHERE ID = ID);

[NeoPa]

I must assume that the actual situation is a little harder to explain so the simpler expedient of comparing the Max() to a specific value (that I suggested) wouldn't work in real life. In that case you've come up with a clever work around.

Typically, I wouldn't recommend using a subquery where it can be done another way but, as I say, I expect it is necessary in your case in spite of the simplified question.

Congratulations on getting by with a simplified question too. So many that try end up causing more confusion than they avoid, but in this case it worked well.