join query problems - 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 2000 Forums

Transact-SQL (2000)

join query problems

Author

Topic

rkalyani
Starting Member

30 Posts

Posted - 2008-03-18 : 14:41:53

I have two tables.

Emp Table

EmpNo FName LName
1 test test
2 test2 test2
3 test3 test3

ProjTable

P Mgr emp Client
1 1 2 3
2 2 3 1

When I join the tables I want the projTable to get the right First and last name from the employee Table.

So my join should produce result like this
p1 mgr is test test, emp is test2 test2 and Clinet is test 3 test 3
I am not able to do that. Can some one help me.

Thank you,
kal30

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-03-18 : 15:28:36

If I understand the problem correctly, you will need to do a left outer join... i.e. get everything from the employee table even if some thing does not exist in the project table....so that all employees are listed even if the project exists or not....

rkalyani
Starting Member

30 Posts

Posted - 2008-03-19 : 11:54:15

SELECT proj.emp AS emp, proj.mgr AS Mgr, Emp.L_NAME, Emp.F_NAME, FROM proj LEFT OUTER JOIN
Emp ON proj.EmpID = Proj.EMP_NO AND proj.Mgr= Emp.EMP_NO

This only returns values if Mgr and Emp are the same empNo. If not it returns a null. So I am not sure how to proceed now.

I have values in projTable where manager and employees have different value.

Can some one please tell me how to do it.

Thank you,
kal30

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-03-19 : 12:09:38

Your original post is not very clear on what you want to accomplish.
If you want all the data from employee table regardless of daat in 2nd table then you will use left outer join. If you want all the data in the project table regardless of the fact that data exists in the first table then you will use right outer join. If you want to have a contraint where only show a matching data between employee and project table based on empid then you will only join by employee id.

rkalyani
Starting Member

30 Posts

Posted - 2008-03-19 : 12:45:10

I need to match employeeID but I need to do it so that I get the right first and last name. So if the client ID is 3 and emp is 2 and Manager is 1 then I want the query to return for client name "test3" empName as "test2" and Manager as "test".

I basically have three columns in a table as foregin key which relates to one column in my Main table.

Please let me know if I am not clear. Thank you so much for the reply.

Thank you,
kal30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources