Query Multiple Columns Within a Table

Question

I have a table of employees that are formatted as follows:

EMPLOYEE (FNAME,MINIT,LNAME,SSN(PK),BDATE,SUPERSSN(NULLABLE))

I need to query every employee and retrieve the following information:

FNAME(employee),LNAME(employee),SUPERSSN,(super)FNAME,(super)LNAME

UPDATED

After running this query:

SELECT A.FNAME,A.LNAME,A.SUPERSSN,B.FNAME,B.LNAME 
FROM EMPLOYEE 
A LEFT JOIN EMPLOYEE B 
ON A.SUPERSSN = B.SSN;

The results were close, but when the superssn was null (CEO/Boss) it caused the remaining rows to populate as null also and did not populate with the actual supervisors ssn. I'm trying to use an IF statement to fix the problem with having a SuperSSN that is null, but I'm receiving the error: ORA-00905: missing keyword.

Below is the query that I ran that generated the error.

SELECT A.FNAME,A.LNAME,A.SUPERSSN,B.FNAME,B.LNAME 
FROM EMPLOYEE A LEFT IF A.SUPERSSN <> 'NULL'
JOIN EMPLOYEE B ON A.SUPERSSN = B.SSN;

@Tamil Selvan - I wasn't trying to indent that as code. It's sole purpose was for a simple reference to what I am trying to accomplish through a query. — JRW2252
– JRW2252, Commented Apr 4, 2014 at 18:02

rene · Accepted Answer · 2014-04-04 18:50:30Z

1

Select A.FName, 
       A.LNAme, 
       A.SuperSSN, 
       B.FName, 
       B.LName 
from Employee A 
Left Join Employee B
          On A.SuperSSN = B.SSN

edited Apr 4, 2014 at 18:50

rene

42.6k78 gold badges122 silver badges166 bronze badges

answered Apr 4, 2014 at 17:46

ajay gandhi

5634 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JRW2252 Over a year ago

@ a jay gandhi - I cleaned it up a little bit and ran it. SELECT A.FNAME,A.LNAME,A.SSN,A.SUPERSSN,B.FNAME,B.LNAME FROM EMPLOYEE A LEFT JOIN EMPLOYEE B ON A.SUPERSSN = B.SSN; It worked up until it hit a row that had an employee that did not have a supervisor (boss), and then it produced null results after that for all B.FNAME AND B.LNAME.

JRW2252 Over a year ago

@Tamil Selvan - Would you like to help with something other than policing grammar? It would be greatly appreciated. Thank you.

ajay gandhi Over a year ago

So, you don't want to display those employees that don't have supervisor? If that is the case, then you can replace Left Join with Inner Join.- Thanks

JRW2252 Over a year ago

That is correct. I also used Inner Join and I retrieved similar results as before. The problem that I am getting is that once a employee gets processed that has no supervisor, all employees that follow show as not having a supervisor also. So they are being truncated basically. Not making any sense to me, so I'm contemplating on just creating a view and trying to query it that way.

ajay gandhi Over a year ago

I am wondering how inner join can bring out the same results as the left join. As far as If statement is concerned, you can try SELECT A.FNAME,A.LNAME,A.SUPERSSN,B.FNAME,B.LNAME FROM EMPLOYEE A LEFT JOIN EMPLOYEE B ON A.SUPERSSN = B.SSN where A.SUPERSSN is not NULL

Collectives™ on Stack Overflow

Query Multiple Columns Within a Table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related