how to get this out put

Hi i have a table call
EmpTran looks like this
EmpId StDate TranID
1 NULL 124
1 01/01/2007 145
2 01/02/2004 147
3 NULL 7844

i have some condition to apply this table like
i need to get all EmpId where StDate is NULL
But problem is if EmpId get repeated more than onece then i need to check if any of that record has got any StDate if it is then i need to exclude that every record
EmpId StDate TranID
1 NULL 24 -should not include cos same EmpId got StDate in the second row
1 01/01/2007 45 -should not include cos this EmpId got StDate
2 NULL 147 -should include cos StDate IS NULL
2 NULL 187 -should include cos StDate IS NULL
3 NULL 7844 -should include cos he got one row and StDate IS NUL

so once the conditions satisfy output looks like this
EmpId StDate TranID
2 NULL 147
2 NULL 187
3 NULL 7844

Any Idea ?
regards
suis

Maybe something like:

Code Snippet

declare @.mockup table
( EmpId integer,
StDate datetime,
TranID integer
)

insert into @.mockup
select 1, null, 124 union all
select 1, '1/1/7', 145 union all
select 2, null, 147 union all
select 2, null, 187 union all
select 3, null, 7844
--select * from @.mockup

select * from @.mockup a
where StDate is null
and not exists
( select 0 from @.mockup b
where a.EmpId = b.EmpId
and StDate is not null
)

/*

EmpId StDate TranID
-- - --
2 NULL 147
2 NULL 187
3 NULL 7844
*/

|||Hi Kent,
That's a great SQL ,
Thanks very much for u r fast response,still i am in a SQL learing period,
I think in the future i might be able to write my own complex SQL without any problems.
This is a nice forum to learn and sort out problems.

many thanks to Microsoft team,
best regards
suis|||

This should get you your desired output:

Code Snippet

SET NOCOUNT ON

DECLARE @.EmpTran table
( EmpId int,
StDate datetime,
TranID int
)

INSERT INTO @.EmpTran VALUES ( 1, NULL, 24 )
INSERT INTO @.EmpTran VALUES ( 1, '01/01/2007', 45 )
INSERT INTO @.EmpTran VALUES ( 2, NULL, 147 )
INSERT INTO @.EmpTran VALUES ( 2, NULL, 187 )
INSERT INTO @.EmpTran VALUES ( 3, NULL, 7844 )

SELECT *
FROM @.EmpTran
WHERE EmpID IN (SELECT EmpID
FROM @.EmpTran
GROUP BY EmpID
HAVING max( isnull( StDate, 0 ) ) = 0
)

EmpId StDate TranID
-- --
2 NULL 147
2 NULL 187
3 NULL 7844