Joining of Tables

Hi,

I just want to ask if the below is scenario is possible:

<code>

SELECT a.EMPNO,a.ENAME,o.Supempno
FROM employee a
INNER JOIN Orgtable o ON o.empno = a.empno
WHERE a.last_name = ‘Santos’
AND o.empno = a.empno;

On the above, query, it will fetch the employee number, employee name, and the employee number of his superior. On employee table, it contains the individual data of the employees including the empno, and ename. while on Orgtable contains empno, and its superior employee number.

I want to display the empno, ename, superior’s empno, and superior’s name of Mr. Santos. But unfortunately, I can’t display the name his superior. I can get the name from employee table.

Please help me…

Thanks

SELECT theguy.empno
     , theguy.ename
     , theboss.empno AS supempno
     , theboss.ename AS supename
  FROM employeeinfo AS theguy
INNER 
  JOIN Orgstaff 
    ON Orgstaff.empno = theguy.empno
INNER
  JOIN employeeinfo AS theboss
    ON theboss.empno = Orgstaff.supempno
 WHERE theguy.ename = 'Santos'

Can you display the output of a “SHOW CREATE TABLE” for the tables concerned?

Wow… sir, Thank you so much…
It really works…

Thank you so much again for your help.

God Bless

SELECT theguy.empno
     , theguy.ename
     , theboss.empno AS superior_empno
     , theboss.ename AS superior_ename
  FROM employee AS theguy
INNER 
  JOIN Orgtable 
    ON Orgtable.empno = theguy.empno
INNER
  JOIN employee AS theboss
    ON theboss.empno = Orgtable.Supempno
 WHERE theguy.last_name = 'Santos'

:slight_smile:

Employee Table:
CREATE TABLE employeeinfo (
empno char(9) ‘Employee’‘s ID Number’,
EmpName varchar(135) default NULL COMMENT ‘Employee Name, use this instead of Personal Info Name’);

Orgstaff Table:

CREATE TABLE orgstaff (
empno char(9) NOT NULL,
supempno char(9) NOT NULL default );

Output of the query on the post:

empno ename supempno

M101 MARIA D102
M102 LEAH F103

The output should be:
empno ename supempno supempname

M101 MARIA D102 JAMAICA
M102 LEAH F103 NERRISSA

The name of the superior must be included on the output. We can get the employee name of the superior on employee table.