How To Perform Specific SELF Join Query SQL-CodePudding

I am having a hard time solving this question that I am practicing with.

Retrieve employee name, designation, and email id of those employees who work in the same retail outlet where George works. Do not display the record of George in the result.

I can solve it with this:

SELECT empname, designation, emailid FROM Empdetails WHERE Worksin = (
      SELECT Worksin FROM Empdetails where empname = 'George'
) AND empname <> 'George'

But I am trying to figure out how I would use a self join instead of a nested select. Any help would be appreciated. Thanks in advance!

CodePudding user response：

Here goes your desired self-join:

SELECT ed.empname, ed.designation, ed.emailid 
FROM Empdetails ed inner join Empdetails ed2
     on ed.Worksin = ed2.Worksin and ed2.empname = 'George'
where ed.empname <> 'George';

CodePudding user response：

I will give the answer by stepping through how you'd make this with images to show each step.

Here is the table I created to give you an example: Demo Table (yes, I know I misspelt designation)

Let's start off by creating a self join

Select *
From EmpDetails A, EmpDetails B

Here is the result Result

Now let's filter the duplicate rows form each table and combine Worksin

Select A.empName, A.designation, A.emailId
From EmpDetails A, EmpDetails B
Where A.empName <> B.empName and A.Worksin = B.Worksin

Here is the result Result

Lastly will will filter the empName "George" from the right side (table B) form the data set. This will leave us with George's co-workers. This is also where you select the fields you want to be shown in the last dataset.

Select A.empName, A.designation, A.emailId
From EmpDetails A, EmpDetails B
Where A.empName <> B.empName and A.Worksin = B.Worksin and B.empName <> "George"

Here is the final table you want: Result