Joining of two statements - 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

Joining of two statements

Author

Topic

OKIDOKI
Starting Member

3 Posts

Posted - 2014-06-25 : 16:40:18

Hi

I am having difficulty with joining these two statements and I just can't seem to get a way. I hope one of you's can be of assistance.

Here is the query:

select Name,Surname,position
from dbo.Employee, dbo.Stafflink
where dbo.Employee.ID = dbo.Stafflink.empID
group by Name,Surname,Position

select Employee.Name as ManagerName, Employee.Position as ManagerPosition
FROM dbo.Employee, dbo.Stafflink
WHERE dbo.Employee.ID = dbo.Stafflink.ManID

How can I join both of these statements??? Please help as I am still new to SQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-25 : 16:47:47

Why are you using a GROUP BY in the first query?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-25 : 17:02:14

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

OKIDOKI
Starting Member

3 Posts

Posted - 2014-06-25 : 18:35:23

@tkizer
Sorry I have been trying so many things and I accidently left that in statement, please ignore it

@Lamprey
Thank you for the links, They have certainly assisted... Unfortunately I need to use a join and not a union, I wish I could use the union for this question

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-25 : 18:46:19

OKIDOKI, those links are to show YOU how to post your question so that WE can help YOU. Please reread the links.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-06-26 : 07:47:15

Try:


SELECT E.Name AS EmpName
	,E.Surname AS EmpSurname
	,E.Position AS EmpPosition
	,COALESCE(M.Name, 'None') AS ManagerName
	,COALESCE(M.Position, 'None') AS ManagerPosition
FROM dbo.Stafflink L
	JOIN dbo.Employee M
		ON L.ManID = M.ID
	RIGHT JOIN dbo.Employee E
		ON L.empID = E.ID;

OKIDOKI
Starting Member

3 Posts

Posted - 2014-06-26 : 09:58:35

Thank you all for your assistance, I finally got it...

select Employee.Name,Employee.Surname,Employee.Position,Manager.name as managerName,manager.Position as managerposition
from(( Employee
inner join Stafflink
on Employee.ID=Stafflink.empID)
inner join Employee manager
on manager.ID=Stafflink.ManID)
order by Employee.id

Ifor I also tried yours and it worked beautifully, thank you.... I must learn more about the COALESCE as it is the first I have heard of it or seen in... Thank you all...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources