I'm looking for SQL code to do the following.

TableA
GrpID,IndID,Locked

50001,10001,0
50001,10002,0

50002,10003,0
50002,10004,1
50002,10005,0

50003,10006,0
50003,10007,0
50003,10008,0

50004,10009,0
50004,10010,0
50004,10011,1
50004,10012,1


I would like to return GrpID's where All IndID have Locked = 0

Recordset
GrpID

50001
50003

Email me at the following:

strat_53711@yahoo.com

SELECT GrpID
FROM TableA
GROUP BY GrpID
HAVING MAX(Locked)=0

Mr Tea

"Gregory S Moy" <moy@epi.ophth.wisc.edu> wrote in message
news:MPG.1c9fcf0238e691e6989681@news.doit.wisc.edu ...
> I'm looking for SQL code to do the following.
>
> TableA
> GrpID,IndID,Locked
>
> 50001,10001,0
> 50001,10002,0
>
> 50002,10003,0
> 50002,10004,1
> 50002,10005,0
>
> 50003,10006,0
> 50003,10007,0
> 50003,10008,0
>
> 50004,10009,0
> 50004,10010,0
> 50004,10011,1
> 50004,10012,1
>
>
> I would like to return GrpID's where All IndID have Locked = 0
>
> Recordset
> GrpID
>
> 50001
> 50003
>
> Email me at the following:
>
> strat_53711@yahoo.com
>

[posted and mailed]

Gregory S Moy (moy@epi.ophth.wisc.edu) writes:
> TableA
> GrpID,IndID,Locked
>
> 50001,10001,0
> 50001,10002,0
>
> 50002,10003,0
> 50002,10004,1
> 50002,10005,0
>
> 50003,10006,0
> 50003,10007,0
> 50003,10008,0
>
> 50004,10009,0
> 50004,10010,0
> 50004,10011,1
> 50004,10012,1
>
>
> I would like to return GrpID's where All IndID have Locked = 0

SELECT GrpID
FROM TableA
GROUP BY GrpID
HAVING MAX(convert(tinyint, Locked)) = 0

This query is untested, as you did not include CREATE TABLE and
INSERT statements.

> Email me at the following:
>
> strat_53711@yahoo.com

Had you included that address as a Reply-To in the header, this is
where the courtsey copy would have gone.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp