How to get this output

hi their
i create a temp table called tempEmpTran and it content a fileds like
Refrlno Description opt-refno

1474 New 5
1474 followup 3
1474 followup 3
1470 new 7

No my problem is i need to eleminate the refrlno which Description <> 'followup',but again the problem is if i see any referrals which repeating more than once then i need to check if any of that Rrefrlno got any Description which equal to the followup, if yes i need to eliminate the whole Referealno from the list,

Refrlno Description opt-refno
1474 New 5 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1474 followup 3 -should not include same refrlno got followup
1470 new 7 -should include,cos its got description 'new'

so once all these condition satisfied then the output should looks like this

Refrlno Description opt-refno
1470 new 7

Any Idea ?
regards
Nirangatry this:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Formatting seems to be screwed on that post, heres the query again:

Code Snippet

select tempEmpTran.*
from tempEmpTran
inner join

(select refrlno, count(*)
from tempEmpTran
group by refrlno
having count(*) > 1) t1
on t1.refrlno = tempEmpTran.refrlno

|||Hi sam,
Many thanks to u r fast respone,but in that query there is not line which eleminate the Description='Followup' ?
any idea ?
regards
Niranga|||Have you ran it?

It only returns records who have a single refrlno... so there shouldn't be any need to filter based on your sample data.

Have a play and try to understand what it does. Then you can expand on it |||

Hey, you could do this 2 ways:

Select *

from tempEmpTran

Where Refrlno not in (Select Refrlno from tempEmpTran where Description = 'Followup')

Select *

from tempEmpTran a

Where not exists (Select Refrlno from tempEmpTran b where Description = 'Followup' and a.Refrlno = b.Refrlno)

BobP

|||Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga|||

Niranga wrote:

Hi i ran a query which u produced me ,but i am getting a error like
"No column was specified for column 2 of 't1'."
i couldn't sort this out ?
any idea ?
regards
niranga

Sorry about that, didn't bother creating the tables and testing. You just need to add an alias to the column name
count(*) becomes
count(*) as [Cnt]|||hi,

here's another alternative.
SELECT *
INTO #tempEmpTran
FROM (
SELECT 1474 AS Refrlno
, 'New' AS Description
, 5 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1474 AS Refrlno
, 'followup' AS Description
, 3 AS [opt-refno]
UNION ALL
SELECT 1470 AS Refrlno
, 'New' AS Description
, 7 AS [opt-refno]
) a

SELECT a.*
FROM #tempEmpTran a INNER JOIN
(
SELECT Refrlno
, IsNew = SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END))
FROM #tempEmpTran
GROUP BY
Refrlno
HAVING SUM((CASE WHEN Description = 'New' THEN 0 ELSE 1 END)) = 0
) b ON a.RefrlNo = b.RefrlNo

DROP TABLE #tempEmpTran|||

here you go..

Code Snippet

Create Table #data (

[Refrlno] int ,

[Description] Varchar(100) ,

[opt-refno] int

);

Insert Into #data Values('1474','New','5');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1474','followup','3');

Insert Into #data Values('1470','new','7');

Select

*

from

#Data Main

Where

Not Exists

(

Select

Sub.[Refrlno]

From

#Data Sub

Where

Main.[Refrlno]=Sub.[Refrlno]

And Sub.[Description] = 'followup'

)