使用联接查询从ManagerName中查找MCode [英] Query to find MCode from the ManagerName using Joins

问题描述

我有两个表，提到的EQDUMP和HRISDetails为:-

EQDump表为:-
ECode Ename
1 A
2 B
3 C
4 D

HRISDetails表格为:-
ECode EName管理器名称
1 A E
2 B E
3 C F
4 D F
5 E G
6 F G

我需要一个查询，该查询为我提供了EqDump中的Empname字段，EQDUmp中的Ename字段和HRISDetails中的ManagerEmpID字段.
输出结果应如下所示:-

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

注意:-经理EmplyoeeID，即MCODe是从HRIS详细信息表中提取的.您可以发现Emplyoee的经理名称"A"为"E"，"E" ECode为5，因此我们必须在输出中显示员工ECOde，员工姓名和经理ECODe.

请向我提供查询(如果可能的话)，因为从事数据库方面工作的人员正在休假，而我只需要在紧急情况下写此即可:(

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:(

推荐答案

您可以使用这个完美的查询

选择HRISDetails.Ecode，HRISDetails.Ename AS EName，EQDump.Ecode AS MCode
来自HRISDetails内部联接
EQDump ON HRISDetails.Managername = EQDump.Ename

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

尝试以下操作:

Try this:

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

输出:

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

Output:

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

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)

这篇关于使用联接查询从ManagerName中查找MCode的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！