Query required 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

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 2:42am

Dharmenrda Kumar Singh

Add a Solution

Comments

Om Prakash Pant 19-Jan-12 9:00am

in HRISDetails table, do you also have managerId which is Ecode? or you need to match with the managername & ename?

4 solutions

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)

Om Prakash Pant · Accepted Answer · 2012-01-19T18:31:00

Solution 2

duplicate post:

Query to find MCode from the ManagerName using Joins[^]

Posted 19-Jan-12 18:31pm

Om Prakash Pant

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2012-01-19T03:04:00

Solution 1

Query should be -

SQL

SELECT eq.ECode, eq.Ename, hri.ManagerEmpID 
FROM EQDump as eq 
INNER JOIN
HRISDetails as hri
ON eq.ECode = hri.ECode

But in table you did not mentioned the column ManagerEmpID.

Posted 19-Jan-12 3:04am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Updated 19-Jan-12 3:16am

v2

Comments

Dharmenrda Kumar Singh 19-Jan-12 23:38pm

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.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Jan-12 0:51am

Ok....

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

Solution 3

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)

<pre lang="SQL">

Posted 19-Jan-12 18:31pm

Dharmenrda Kumar Singh

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

Solution 4

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:35pm

amolpatil2243