Query to find MCode from the ManagerName using Joins

Question

0.00/5 (No votes)

See more:

I have two table as mentioned EQDUMP and HRISDetails as:-

EQDump table as:-
ECode Ename
1 A
2 B
3 C
4 D

HRISDetails Table as:-
ECode EName managername
1 A E
2 B E
3 C F
4 D F
5 E G
6 F G

I need a query which gives me the field Empname from EqDump,Ename from EQDUmp and ManagerEmpID from HRISDetails.
The Out put should be look like this:-

ECode Ename MCode
1 A 5
2 B 5
3 C 6
4 D 6

Note:- Manager EmplyoeeID i.e MCODe is extracted from HRIS Details Table.You can find that the manager name for Emplyoee 'A" is 'E' and 'E' ECode is 5 so in the output we have to display the ECOde for Employee, Employeename and the ECODe of Manager.

Please provide me the query if possible as the guys who are working on database side is on leave and i only have to write this on urgent basis:(

Posted 19-Jan-12 17:41pm

Dharmenrda Kumar Singh

Add a Solution

4 solutions

Solution 3

Try this:

SQL

select e.Ecode, e.Ename , hh.Ecode as Mcode   from EQDump e
join HRISDetails h on e.Ename = h.Ename
join HRISDetails hh on h.managername = hh.ename

Output:

ECode Ename MCode
1 A 5
2 B 5
3 C 6
4 D 6

Posted 19-Jan-12 18:44pm

Om Prakash Pant

Comments

Tarakeshwar Reddy 20-Jan-12 0:48am

Looks like we both posted at the same time. 5'ed your solution

Om Prakash Pant 20-Jan-12 0:53am

yes :-)
i was looking at multiple posts of same question

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

amolpatil2243 · Accepted Answer · 2012-01-19T18:29:00

Solution 1

hi,

You can use this perfect query

SELECT HRISDetails.Ecode, HRISDetails.Ename AS EName, EQDump.Ecode AS MCode
FROM HRISDetails INNER JOIN
EQDump ON HRISDetails.Managername = EQDump.Ename

Posted 19-Jan-12 18:29pm

amolpatil2243

Updated 19-Jan-12 18:32pm

v4

Comments

Tarakeshwar Reddy 20-Jan-12 0:45am

Have you tried the solution? It will not return any rows. HRISDetails has manager name of E, F and G, whereas EQDump table does not have those rows. Look at my solution posted.

amolpatil2243 20-Jan-12 0:53am

i wrote the solution because of every manager is a employee and he listed in employee table

Dharmenrda Kumar Singh · Accepted Answer · 2012-01-19T18:30:00

Solution 2

SQL

select A.EmployeeName,A.Ecode,B.ECode as ManagerECode from HRISDetails as A inner join
HRISDetails as B on
A.ImmediateSupervisor=B.EmployeeName where A.Ecode in (Select SUBSTRING(ReporteeUserID,4,8) from EQDump)

Posted 19-Jan-12 18:30pm

Dharmenrda Kumar Singh

Tarakeshwar Reddy · Accepted Answer · 2012-01-19T18:46:00

Solution 4

SQL

select EQDump.ECode, EQDump.EName, HRIS2.Ecode as MCode
From EQDump
inner join HRISDetails HRIS1 on EQDump.ECode = HRIS1.ECode
inner join HRISDetails HRIS2 on HRIS1.managername = HRIS2.EName

Posted 19-Jan-12 18:46pm

Tarakeshwar Reddy