SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 New To SQL - Please Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ChumChum
Starting Member

South Africa
3 Posts

Posted - 03/13/2014 :  06:25:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/13/2014 :  07:33:25  Show Profile  Reply with Quote
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
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 03/13/2014 :  07:34:52  Show Profile  Reply with Quote

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
Go to Top of Page

ChumChum
Starting Member

South Africa
3 Posts

Posted - 03/13/2014 :  08:13:56  Show Profile  Reply with Quote
Thanks for the reply Mik and Veera. You guys are super awesome it works. I need to master inner join statements.

Thanks Guys
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 03/13/2014 :  08:31:14  Show Profile  Reply with Quote
Welcome

Veera
Go to Top of Page

ChumChum
Starting Member

South Africa
3 Posts

Posted - 03/13/2014 :  10:51:56  Show Profile  Reply with Quote
I am trying to teach myself SQL but its not going so good.
I need help again using the above tables, how do i get these outputs

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


And how do i get this output

|StaffCount | Name |
--------------------------------------
| 4 | Daizy |
--------------------------------------
| 3 | Scrooge |
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000