Help in Query (having count(*) = 1) sort of thing

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help in Query (having count(*) = 1) sort of thing

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-09-18 : 07:02:29

select distinct JobID,fkErrorInfoCode from ImportJobError, ImportJob,ErrorMaster
where fkJobID = JobID
and ErrorInfoCode = fkErrorInfoCode
and ErrorMaster.ermMessageType ='ERR'
order by JobID

Here i am getting Result as

JobID fkErrorInfoCode

sb001634 VLD_49077
sb001635 VLD_49077
sb001635 VLD_51041
sb001636 VLD_49077
sb001637 VLD_49077
sb001638 VLD_49077
sb001639 VLD_49077
sb001640 VLD_49077
sb001640 VLD_51041
sb001641 VLD_49077
sb001642 VLD_49077
sb001643 VLD_49077
sb001644 VLD_49077
sb001645 VLD_49077

Now what I wanted is to just show result where there is only one possible Error Like

JobID fkErrorInfoCode

sb001634 VLD_49077
sb001636 VLD_49077
sb001637 VLD_49077
sb001638 VLD_49077
sb001639 VLD_49077
sb001641 VLD_49077
sb001642 VLD_49077
sb001643 VLD_49077
sb001644 VLD_49077
sb001645 VLD_49077

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-18 : 08:12:26

[code]
select JobID,fkErrorInfoCode
from
(
select COUNT(1) OVER (PARTITION BY JobID) AS Occur,JobID,fkErrorInfoCode
from
(
select distinct
JobID,fkErrorInfoCode
from ImportJobError, ImportJob,ErrorMaster
where fkJobID = JobID
and ErrorInfoCode = fkErrorInfoCode
and ErrorMaster.ermMessageType ='ERR'
order by JobID
)t
)p
WHERE Occur=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/