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

how to get optimized join

Hi Experts,
I have following doubts on join condition
Table 1 primary key (id,sub)
Name id sub marks

xxx 61 maths 45
xxx 61 science 50

another table primary key ( id,language)

id language write
61 english yes
61 Hindi no
Output:
Xxx 61 maths 45 english yes
Xxx 61 maths 45 Hindi no
Xxx 61 science 50 english yes
Xxx 61 science 50 hindi no
how to join these tables to get
every information in 2 rows will it possible

xxx 61 maths 45 English yes
xxx 61 science 50 hindi no

please suggest me to right path
thanking u
please mail to me:nallisalmon@.yahoo.co.in

I don′t see the logic which one has to be excluded from the output results ? Is it just randomly or is there a deeper logic implemented ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de