CODEWITHSUNDEEP
sql-server
dmegha
dmegha

Reputation: 88

Select records when 2 column's data will match

I have two tables as shown below:

 -----------------------
 |EmpNo|Complaint      |
 -----------------------
 |9091 |Change required|
 |9092 |No change      |
 |9093 |Changes done   |
 -----------------------

Above table contains employee number and his complaints.

I have one another table which contains employee all kind of details as shown below.

-------------------------------
|EmpNo|EmailID      |EmpBossNO|
-------------------------------
|9091 |[email protected]|9092     |
|9092 |[email protected]|9093     |
|9093 |[email protected]|9099     |
-------------------------------

Here, if Empno:9091 will raise any complain then a mail will send to his boss that the complain is raise by your employee and you have to accept it so I want to get EmailID of employee's boss and for that I want one SQL query. I tried the query shown here, but it doesn't work. 

select EmpEmailID
from tblComplaint
inner join tblEmpMaster on tblEmpMaster.EmpNo = tblComplaint.EmpPSNo
where tblComplaint.EmpPSNo = tblEmpMaster.EmpBossNo

I want output like.. if complaint is raised by EmpNo:9091 then it will return EmailID of his boss which is [email protected].

Upvotes: 2

Views: 54

Answers (3)

Hemanth Mannam
Hemanth Mannam

Reputation: 72

you can even use sub queries to get the Email_Id of the boss as shown below

SELECT Email_Id 
FROM EMP_Details 
WHERE Emp_No IN (
         SELECT Boss_Id 
         FROM Emp_Details) AND 
      Emp_No IN (
         SELECT Emp_No 
         FROM Emp_Complaints)

Upvotes: 0

Rasanjana N
Rasanjana N

Reputation: 1400

You should self join tblEmpMaster

select boss.EmpEmailID
from tblComplaint
inner join tblEmpMaster emp on emp.EmpNo = tblComplaint.EmpPSNo
inner join tblEmpMaster boss on boss.EmpNo = emp.EmpBossNO
where tblComplaint.EmpPSNo = 9091

DB Fiddle

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You are on the right track with a join between the tblComplaint and tblEmpMaster tables. But, you need an additional join to tblEmpMaster to bring in the boss' email for each employee complaint.

SELECT
    c.EmpNo,
    c.Complaint,
    COALESCE(e2.EmailID, 'NA') AS boss_email
FROM tblComplaint c
INNER JOIN tblEmpMaster e1
    ON c.EmpNo = e1.empNo
LEFT JOIN tblEmpMaster e2
    ON e1.EmpBossNO = e2.EmpNo;

enter image description here

Demo

I used a left self join above, in case a given employee does not have a boss (e.g. for the highest ranking boss). In this case, I display NA for the boss email.

Upvotes: 6

Related Questions