Obtaining new/old values from two seperate employee records in same table

Question

I want to print one row of data for each employee in a database table, and each employee has 2 rows of data, one for their current job positon, and the other for their backup position.

The Employee table looks like this for each employee:

|EMPLNO| FNAME | LNAME    |JOBNO| TITLE | TITLE_DT  |STATUS|
------------------------------------------------------------
|  577 | Jon   | Albeary  | 1   | 54737 | 8/15/2016 | C    |
------------------------------------------------------------
|  577 |  Jon  | Albeary  | 2   | 8297A | 4/1/2019  | T    |
------------------------------------------------------------

The results should look like this:

|EMPLNO| FNAME | LNAME    |JOBNO| TITLE | TITLE_DT  |STATUS| BKUP_TITLE | BKUP_TITLE_DT  |BKUP_STATUS|
---------------------------------------------------------------------------------------------------------
|  577 | Jon   | Albeary  | 1   | 54737 | 8/15/2016 | C    | 8297A      | 4/1/2019       | T         |
---------------------------------------------------------------------------------------------------------

The SQL requirement is to look for all records with (JOBNO = '1') AND(EMPL_CLASS <> 'A') but have a record with (JOBNO = '2') AND (TITLE = '54737'). I obtained the results, but don't know how to obtain the information from the second record to have it listed on one row.

My SQL looks like this:

SELECT EMPLNO, FNAME, LNAME, STATUS, TITLE, TITLE_DT 
FROM  EMPLOYEE
WHERE (EMPLNO IN
           (SELECT EMPLNO
            FROM  EMPLOYEE AS EMPLOYEE_1
            WHERE (JOBNO = '2') AND (TITLE = '54737'))) 
AND (JOBNO = '1') AND (STATUS <> 'A');

However, that query only gives me the record with JOBNO = 1. Can anyone help me write a query to print the data from both records with JOBNO = 1 and JOBNO = 2 on a single line?

Thank you.

Answer 1

You could do a join to the table itself by first selecting all the records for jobno = 2, then joining that to the records where jobno = 1 where the employee number matches.

    select a.emplno, a.fname, a.lname, a.status, b.title as bkup_title, b.title_dt as bkup_title_dt, b.status as bkup_Status
    from (select * from employee where jobno = '2' and title = '54737') b
    join employee a
    on a.emplno = b.emplno
    wherre a.jobno = '1' and a.status <> 'A';