JOIN tables - 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

Database Design and Application Architecture

JOIN tables

Author

Topic

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2008-12-30 : 14:02:55

Lets say I have a table Task with column assignedEmployeeId and terminatedByEmployeeId

I need to create a View something like

select * from Task
inner join Employee on Employee.id = Task.assignedEmployeeId
inner join Employee on Employee.id = Task.terminatedByEmployeeId

I was wondering if there isn't another syntax, like in one line ....???I'm pretty noob in sql sorry

Thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:13:21

did you mean this


SELECT *
FROM
(SELECT assignedEmployeeId AS EmpID,otherfields... FROM Task
UNION ALL
SELECT terminatedByEmployeeId,...  FROM Task
)t
INNER JOIN Employee e
ON e.id =t.EmpID

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2008-12-30 : 14:25:55

hmm the above seem complicated, let me correct the sql

select t.*, e1.employeeName, e2.employeeName from Task AS t
inner join Employee AS e1 on e1.id = t.assignedEmployeeId
inner join Employee AS e2 on e2.id = t.terminatedByEmployeeId

hope it's clearer ... maybe it's just me, i don't get the union thing

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 14:54:27

What problem are you having?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 15:04:32

quote:
Originally posted by GoDaddy

hmm the above seem complicated, let me correct the sql

select t.*, e1.employeeName, e2.employeeName from Task AS t
inner join Employee AS e1 on e1.id = t.assignedEmployeeId
inner join Employee AS e2 on e2.id = t.terminatedByEmployeeId

hope it's clearer ... maybe it's just me, i don't get the union thing

so how do you want to modify above query?

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2008-12-30 : 15:39:27

Im not having a problem per say.

Just wondering if there is a more elegant way maybe?
A better way?

Or this is usually how is done?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 16:22:20

You haven't told us what you want to do, so it's hard for us to say if there is a better way.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2008-12-30 : 16:26:22

oh,

I just want to select the name of the employee assign to the task and also select the name of the employee that has terminated the task

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 16:30:27

I can only make an assumption as you haven't shown us your tables or provided any sample data.

select e1.name, e2.name
from Task t
inner join Employee e1 on e1.id = t.assignedEmployeeId
inner join Employee e2 on e2.id = t.terminatedByEmployeeId

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2008-12-30 : 17:06:56

ok thanks, its the same select as I did, I guess that's the usual way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources