CODEWITHSUNDEEP
sqlpostgresql
Beauregard Lionett
Beauregard Lionett

Reputation: 321

Same outputs come from query where different outputs expected

I'm using my downtime to learn SQL (Postgres) since I'm quite bad at it. I appreciate the patience and guidance.

I have two tables, table employee: 

  fname   | minit |  lname  |    ssn    |   bdate    |         address         | sex |  salary  | super_ssn | dno 
----------+-------+---------+-----------+------------+-------------------------+-----+----------+-----------+-----
 James    | E     | Borg    | 888665555 | 1937-11-10 | 450 Stone, Houston TX   | M   | 55000.00 |           |   1
 John     | B     | Smith   | 123456789 | 1965-01-09 | 731 Fondren, Houston TX | M   | 30000.00 | 333445555 |   5
 Franklin | T     | Wong    | 333445555 | 1955-12-08 | 638 Voss, Houston TX    | M   | 40000.00 | 888665555 |   5
 Alicia   | J     | Zelaya  | 999887777 | 1968-01-19 | 3321 Castle, Spring TX  | F   | 25000.00 | 987654321 |   4
 Jennifer | S     | Wallace | 987654321 | 1941-06-20 | 291 Berry, Bellaire TX  | F   | 43000.00 | 888665555 |   4
 Ramesh   | K     | Narayan | 666884444 | 1962-09-15 | 975 Fire Oak, Humble TX | M   | 38000.00 | 333445555 |   5
 Joyce    | A     | English | 453453453 | 1972-07-31 | 5631 Rice, Houston TX   | F   | 25000.00 | 333445555 |   5
 Ahmad    | V     | Jabbar  | 987987987 | 1969-03-29 | 980 Dallas, Houston TX  | M   | 25000.00 | 987654321 |   4

And table department:

     dname      | dnumber |  mgr_ssn  | mgr_start  
----------------+---------+-----------+------------
 Research       |       5 | 333445555 | 1988-05-22
 Administration |       4 | 987654321 | 1995-01-01
 Headquarters   |       1 | 888665555 | 1981-06-19

I understood (mostly) of how joins work, but I'm stuck at self-joins.

What I want with this was to return a view that had employee name, supervisor name, and employee salary for each employee who works in the 'Research' department.

When I tried my hand at the self-joins, I'm doing something obviously wrong that I cannot pinpoint:

SELECT e.fname, e.lname, s.fname, s.lname, e.salary
FROM employee e JOIN employee s
ON e.ssn = s.super_ssn
JOIN department d 
ON d.dnumber = e.dno
WHERE d.dname = 'Research'

This returns three times the employee named Franklin Wong who is a manager. But if you see employee.dno, you will see that there are four people who work in the Research department, since the dno equivalent to 5 is the same dnumber in the department table.

What am I doing wrong here? Please and thank you.

Edit 1: I adjusted the SELECT statement and now I see the other employees. So now the question is how do I get their salaries.

Edit 2: The answer from Gordon, but with adjustments to get the manager's name instead of their super_ssn.


SELECT e.fname, e.lname, s.fname, s.lname, e.salary
FROM employee e JOIN
     employee s
     ON s.ssn = e.super_ssn JOIN
     department d 
     ON e.dno = d.dnumber
WHERE d.dname = 'Research'

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think your JOIN conditions have the tables reversed. And, you want to join on the department number not on the SSN:

SELECT e.fname, e.lname, s.super_ssn, e.salary
FROM employee e JOIN
     employee s
     ON s.ssn = e.super_ssn JOIN
     department d 
     ON e.dno = d.dnumber
WHERE d.dname = 'Research'

Upvotes: 1

Related Questions