Script Help

1

Hi

Hi have the following script that returns

3825042 8
3825042 9

What i need is

3825042 2

I have tried using a count and distinct but always get the value 3 rather than 2

select

COM.CompID,
CVF.VerificationSourceTypeID

from
Comp COM (nolock)
left outer join CompEntity CE (nolock) on CE.CompID = COM.CompID
left outer join [dbo].[CompVerificationSource] CVF (nolock) ON CVF.compid = COM.CompID

where

 COM.CompID = '3825042'

Any advice?

2

Maybe:

SELECT COM.CompID
    ,COUNT(DISTINCT CVF.VerificationSourceTypeID) AS IDCount
FROM Comp COM
LEFT JOIN CompEntity CE
    ON CE.CompID = COM.CompID
LEFT JOIN [dbo].[CompVerificationSource] CVF
    ON CVF.compid = COM.CompID
WHERE COM.CompID = '3825042'
--GROUP BY COM.CompID;

Otherwise post DDL with test data and results.

3

Used like a pepper-pot ...

... just checking that you are aware that this will, on occasions:

MISS some rows that are present (and have been in the database, unchanged, for days / months / years ...)
on other occasions will DUPLICATE some rows
and on a much rarer, third, occasion will abort the query with an error when SQL detects a clash that it cannot work around

In those instances your COUNT will be wrong of course.