New To SQL - Please Help - 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

General SQL Server Forums

New to SQL Server Programming

New To SQL - Please Help

Author

Topic

ChumChum
Starting Member

3 Posts

Posted - 2014-03-13 : 06:25:06

Hi, I am new to SQL and kindly need help with the following

I need to output the following view

_______________________________________
|Name |Surname|Position|ManagerName |
---------------------------------------
|Donald |Duck |Support |Scrooge |
---------------------------------------
|Scrooge|McDuck |Manager |Daizy |
---------------------------------------

Below are the tables

table1
id|Name |Surname|Position|
----------------------------
1 |Scrooge|McDuck |Manager |
----------------------------
2 |Daizy |Duck |Manager |
----------------------------
3 |Donald |Duck |Support |
----------------------------

Table2
|EmpID|ManID|
|-----------|
| 3 | 1 |
| 1 | 2 |
-------------

This is my code but it does not output the correct manager name

SELECT * FROM
(SELECT NAME, SURNAME, POSITION
FROM EMPLOYEE, STAFFLINK
WHERE ID = EMPID),

(SELECT NAME AS MANAGERNAME, POSITION AS MANAGERPOSITION
FROM EMPLOYEE, STAFFLINK
WHERE ID = MANID)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-13 : 07:33:25

Check if this works for you

SELECT Table1.Name,Table1.SurName,table1.Position,Manager.Name as ManagerName
FROM Table1
INNER JOIN Table2 ON table1.ID=table2.EmpID
INNER JOIN Table1 Manager ON Maanager.ID=Table2.ManID

Cheers
MIK

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-13 : 07:34:52

DECLARE @table1 TABLE(id INT,Name VARCHAR(20),Surname VARCHAR(20),Position VARCHAR(20))
INSERT INTO @table1 VALUES
(1,'Scrooge','McDuck','Manager'),
(2,'Daizy','Duck','Manager'),
(3,'Donald','Duck','Support')

DECLARE @Table2 TABLE(EmpID INT,ManID INT)
INSERT INTO @Table2 VALUES
(3,1),(1,2)

select t.Name,t.Surname,t.Position,ttt.Name as 'ManagerName'
from @table1 as t
inner join @Table2 as tt
on t.id = tt.EmpID
inner join @table1 as ttt
on ttt.id = tt.manid

Veera

ChumChum
Starting Member

3 Posts

Posted - 2014-03-13 : 08:13:56

Thanks for the reply Mik and Veera. You guys are super awesome it works. I need to master inner join statements.

Thanks Guys

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-13 : 08:31:14

Welcome

Veera

ChumChum
Starting Member

3 Posts

Posted - 2014-03-13 : 10:51:56