>>I would like to use one query to accomplish the following:
>>
>>We have a table in MS SQL Server that is populated when an entry is made on a possible stolen vehicle. there is a status field that will have either a 5 or 7 and a temporary number in the ncic field that starts with 't'followed by 9 other random numbers. There are other records that have other statuses and don't have the 't' in the ncic field. I need to pull everything that doesn't have two records for a given temporary number like the following:
>>
>>ncic status entry_date
>>t12312312 7 01/01/2007
>>t12312312 5 01/02/2007
>>t12312111 7 01/01/2007
>>t12312871 7 01/05/2007
>>
>>the first two records should not appear in the result set because they have both a 7 and a 5, with a matching ncic number. I was thinking a subquery might be in order, otherwise, I'll have to do it in two steps.
>
>
>Something like that:
>
>

>SELECT T.*
>       FROM @Temp T
>INNER JOIN (SELECT Ncic
>                   FROM @Temp T1
>                   WHERE Status IN (5,7)
>                   GROUP BY Ncic
>                   HAVING COUNT(DISTINCT Status) < 2) T3
>ON T.Ncic = T3.Ncic
>

I was thinking it would be something like that, but didn't have a clue as to how it would be structured! Thanks, I'll give it a try.