Condition not in a group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Condition not in a group

Author

Topic

BHMet
Starting Member

2 Posts

Posted - 2006-05-01 : 14:29:39

I need to check groups of records for a condition that doesn't exist in any record in the group.
Example:
Accountno code
1 A
1 C
1 M
1 P
1 Z

If the group does not contain a code 'Z', I want to select the accountno of the group. In the above example, the accountno should not be selected. If none of the records had a code 'Z', the accountno should be selected.

I can't simply say 'where code <> 'Z'. When the system looks at the first record in the above example, it sees that A is not equal to Z and the accountno would be selected. But this is wrong. As there is a 'Z' in the group, the accountno should not be selected.

I've tried using 'top 1' and count. Nothing has worked.
Any ideas?

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-01 : 15:43:18

You can use a pretty simple sub-select like the following:[CODE]select AccountNo
from #temp1
where AccountNo not in (select AccountNo from #temp1 where Code= 'z')[/CODE]

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-01 : 15:48:37

Or if 'Z' really is the option you are looking for and it is the MAX possible value you could do something a bit different like the following without using a sub-select: [CODE]select AccountNo, max(Code)
from #temp1
group by AccountNo
having max(Code) <> 'Z'[/CODE]

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

BHMet
Starting Member

2 Posts

Posted - 2006-05-01 : 17:12:03

Unfortunately neither solution works.

I actually have several codes. The Z was hypothetical...so I can't use this as a max.

In the other case, I ran the query, and it doesn't do it. The system seems to stop on the first record where the conditon isn't met and selects.

The key is that the system needs to look at every record in the group before making a final determination.

I tried something like:

Select accountno, count(code) as code_ct from table1 where
code = 'Z'
group by accountno
having count(code) = 0

In thouse groups where there is no 'Z', nothing gets counted, but from some reason, 0 doesn't seem to be valid and so I get zero counts...nothing is selected.

However, if I code "having count(code) = 1" I get the right result.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 00:46:22

If you dont get required result post some additional sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-02 : 01:19:15

[code]
SELECT accountno
FROM table1
GROUP BY accountno
HAVING SUM(CASE WHEN code = 'Z' THEN 1 ELSE 0 END) = 0
[/code]

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources