Help with a Query Please

[mcbigj]

Hi,
I need to write a query that will pull records that have an Id that appears up to a set number of times and not more. Here's an example:

A table is updated every time there is a signoff done on a project. There are 3 different types of signoffs, the type is entered as well as the ID from another table for the corrosponding project when a record is inserted. I want to return a recordset that only returns records where all three signoffs have NOT been completed. So basically all records where the id is in there once, twice, but not three times.

Any ideas would be appreciated.
Thanks
McBigJ

 

[mwolf00]

SELECT id, count(id) FROM myTable GROUP BY id HAVING count(ID) < 3 Get the Best Answers! faq333-2924
&quot;A witty saying proves nothing.&quot; - Voltaire
mikewolf@tst-us.com

 

[mcbigj]

Thank you. One problem is that the id field is a uniqueIdentifier and I cannot use a ui in an aggregate operation (count(id)). I'm going to try and CAST the id to another datatype in the process and see if that works. Any other ideas other than the CAST?
Thanks again.

 

[tlbroadbent]

Just use Count(*). Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[mcbigj]

Seems to work with the Cast,, thanks a ton.