Using COUNT to check if data exists horribly expensive

[pwills]

I have a table of employees, some of which are supervisors.

(ID,NAME,SUP_ID)

When I list them out, I want to mark which ID's are supervisors. E.g.

ID,NAME,SUP_ID,Y/N

It will display Y if any row has a SUP_ID set to ID.

It seems horribly expensive (n^n) to do a count each time. Surely there is an n^2 or even better solution?

 

[mwolf00]

This should work:


SELECT empID, empName, supID,
CASE supID
WHEN empID THEN 'Y'
ELSE 'N'
END
FROM emp

 

[pwills]

Surely this will check if supID and empID are the same?

Imagine table:

1,Bob,2
2,Bill,<null>
3,Ben,<null>

View should produce

1,Bob,2,N
2,Bill,<null>,Y
3,Ben,<null>,N

 

[pwills]

I was thinking for a list of supervisors

select empid from table t1
where exists (select * from from table where supid = t1.empid)

..but don't know if legal sql

 

[pwills]

do you know if I can have something like:

select empid, empname, supid, case
exists (select * from from table t1 where t1.supid = empid) then 'y' else 'n' end
from table

 

[mwolf00]

You're making it too hard....

SELECT * FROM emp WHERE empID = supID

 

[tlbroadbent]

I believe this will give you the result you seek.

Select
ID, NAME, SUP_ID,
IsSup=Case When Exists
(Select * From Employees
Where SUP_ID=emp.ID)
Then 'Y' Else 'N' End
From Employees emp
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[pwills]

Hi Terry,

Thanks for this post. It does seem to me that is an n^n solution, because sql will search the table n times for each of n employees. But maybe SQL Server is more intelligent than that..

 

[rt63]

If you expect a low number of supervisors, you can try this


select a.empID , a.empName , a.supID , isnull(b.sup_flg,'N') as sup_flg
from emp a left outer join
(select distinct supID , 'Y' as sup_flg
from emp where supID is not null) b
on a.empID = b.supID


However, if the objective is just to get a list of supervisors then just select distinct supID from the table

select distinct supID from emp where supID is not null


RT

 

[tlbroadbent]

pwills,

Correlated sub-queries are quite efficient as long as the search column, SUP_ID, is indexed. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.