sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

sql

Author

Topic

Natalia89
Starting Member

12 Posts

Posted - 2013-12-04 : 16:27:05

I have a table that contains empid, name, salary, hiredate, positionand supervisor (which includes empid not the name) how do i list the empid and name of all supervisors ? the output has to have to columns supervisor( and a list of their emid) and their names. Thats the create statement used to create the employee table:
/* Create table Employee */
IF OBJECT_ID('Employee', 'U') IS NOT NULL
DROP TABLE Employee
GO
CREATE TABLE Employee (
emp_id NCHAR(5),
name NVARCHAR(20),
position NVARCHAR(20),
hire_date DATETIME,
salary MONEY,
bcode NCHAR(3),
supervisor NCHAR(5)
)

i have tried a variety of statements using having statement and count but the dont seem to work:/

select emp_id, name from employee where position='manager'; i tried this but it doesnt work anoune smart that knows how to do it?

saddf

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 16:40:51

select e.emp_id, e.name, s.name as supervisor
from employee e
left outer join employee s on s.emp_id = e.supervisor

Be One with the Optimizer
TG

Natalia89
Starting Member

12 Posts

Posted - 2013-12-04 : 16:42:16

its not the join statement but thanks anyway

saddf

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 16:54:30

sorry, I misread your question. So just all supervisors, right? you don't need who they are supervising? Try this:


select e.emp_id, e.name
from   (
       select supervisor 
       from employee 
       group by supervisor
       ) s
join   employee e on e.emp_id = s.supervisor

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-05 : 02:00:21

[code]
select e.emp_id, e.name
from employee e
where exists (select 1 from employee where supervisor = e.emp_id)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Natalia89
Starting Member

12 Posts

Posted - 2013-12-05 : 15:32:57

Thanks it worked.
see if i wanted to display emp_id without the e only the numbers how do i do that?

saddf

Natalia89
Starting Member

12 Posts

Posted - 2013-12-05 : 15:33:23

what i meant was how do i only display a part of data from the column
lets say emp_id is E3456 and all i want to display is 3456 without that E? any ideas?

saddf

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-05 : 16:25:03

check out String Functions in Books Online.

If it is always the first 1 character then you could use:
stuff(emp_id, 1, 1, '')

if it is always an 'E' anywhere then you could use:
replace(emp_id, 'E', '')

Look through the functions and see which will work best for your situation.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-05 : 16:53:04

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190118#742347

please stop cross posting (posting the same question in multiple threads).
thanks

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources