Combining Two SQL Select Queries with Where Clauses

Question

I have two Oracle queries that I need combined through an inner join where the tables are joined using the person_uid field. This is because I need to compare what an employee's pay, job title, and supervisor was from one year to the next. I need to have the 2015 data and the 2014 data in the same row for each employee, so if this can be done by doing a subquery using an inner join on the person_uid field, that is the method that I believe will accomplish this.

Here is the first query that pulls the necessary 2015 data:

SELECT  person_uid,
    id ,
    position_contract_type,
    position,
    job_suffix,
    position_status,
    effective_date,
    position_employee_class,
    timesheet_organization ,
    appointment_pct ,
    annual_salary ,
    per_pay_salary ,
    hourly_rate ,
    position_title ,
    academic_title ,
    supervisor_id ,
    supervisor_name ,
    supervisor_position ,
    supervisor_job_suffix ,
    supervisor_title ,
    assignment_grade ,
    position_change_reason ,
    position_change_reason_desc
FROM    employee_position_cunm posn
WHERE   posn.position_contract_type = 'P'
AND     posn.position_status <> 'T'
AND     posn.effective_date = (SELECT MAX(effective_date)
                           FROM   employee_position_cunm p2
                           WHERE  p2.person_uid = posn.person_uid
                           AND    p2.position = posn.position
                           AND    p2.job_suffix = posn.job_suffix
                           AND    p2.effective_date <= '01-Nov-2015')
order by person_uid

I need it to be joined to this query on the person_uid field so that each unique ID for the employee has the records for both years in a single row:

SELECT  person_uid,
    id ,
    position_contract_type,
    position,
    job_suffix,
    position_status,
    effective_date,
    position_employee_class,
    timesheet_organization ,
    appointment_pct ,
    annual_salary ,
    per_pay_salary ,
    hourly_rate ,
    position_title ,
    academic_title ,
    supervisor_id ,
    supervisor_name ,
    supervisor_position ,
    supervisor_job_suffix ,
    supervisor_title ,
    assignment_grade ,
    position_change_reason ,
    position_change_reason_desc
FROM    employee_position_cunm posn
WHERE   posn.position_contract_type = 'P'
AND     posn.position_status <> 'T'
AND     posn.effective_date = (SELECT MAX(effective_date)
                           FROM   employee_position_cunm p2
                           WHERE  p2.person_uid = posn.person_uid
                           AND    p2.position = posn.position
                           AND    p2.job_suffix = posn.job_suffix
                           AND    p2.effective_date <= '01-Nov-2014')
order by person_uid

Possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — mentat, Oct 29 '15 at 23:26

score 0 · Answer 1 · answered Oct 29 '15 at 22:11

An easy way would be to use OR:

WHERE posn.position_contract_type = 'P' AND
      posn.position_status <> 'T' AND
      (posn.effective_date = (SELECT MAX(effective_date)
                              FROM   employee_position_cunm p2
                              WHERE  p2.person_uid = posn.person_uid
                                     p2.position = posn.position AND
                                     p2.job_suffix = posn.job_suffix AND
                                     p2.effective_date <= '01-Nov-2014'
                             ) OR
       posn.effective_date = (SELECT MAX(effective_date)
                              FROM   employee_position_cunm p2
                              WHERE  p2.person_uid = posn.person_uid
                                     p2.position = posn.position AND
                                     p2.job_suffix = posn.job_suffix AND
                                     p2.effective_date <= '01-Nov-2015'
                             )
      )

I'm using Oracle and I want it so that it shows all of the columns from both tables. When I run it as a UNION it's dropping the secondary columns and combining them into a singular column, even when I name change the columns — siklootd, Nov 02 '15 at 16:09
Basically, I need all the columns from table A to appear alongside all the columns from table B, joined at the person_uid field so that all the records for the employee run in a single row from left to right. I need to be able to see an employee's record of who their prior supervisor was, what dept they were in, etc alongside what their current info is in the same row. — siklootd, Nov 02 '15 at 16:15

score 0 · Answer 2 · answered Oct 29 '15 at 22:14

In Oracle you could do a UNION or a UNION ALL.

SELECT  person_uid,
    id ,
    position_contract_type,
    position,
    job_suffix,
    position_status,
    effective_date,
    position_employee_class,
    timesheet_organization ,
    appointment_pct ,
    annual_salary ,
    per_pay_salary ,
    hourly_rate ,
    position_title ,
    academic_title ,
    supervisor_id ,
    supervisor_name ,
    supervisor_position ,
    supervisor_job_suffix ,
    supervisor_title ,
    assignment_grade ,
    position_change_reason ,
    position_change_reason_desc
FROM    employee_position_cunm posn
WHERE ...
...
...
UNION ALL
SELECT  person_uid,
    id ,
    position_contract_type,
    position,
    job_suffix,
    position_status,
    effective_date,
    position_employee_class,
    timesheet_organization ,
    appointment_pct ,
    annual_salary ,
    per_pay_salary ,
    hourly_rate ,
    position_title ,
    academic_title ,
    supervisor_id ,
    supervisor_name ,
    supervisor_position ,
    supervisor_job_suffix ,
    supervisor_title ,
    assignment_grade ,
    position_change_reason ,
    position_change_reason_desc
FROM    employee_position_cunm posn
WHERE ....
....
....;

Is there a way to write it a subquery? I'm having trouble converting to a subquery where the 2014 data is a subquery of the 2015 — siklootd, Nov 02 '15 at 21:36
I think you are trying to do a join of two tables (in simple words). Can you update your question with a specific example? The example doesn't need to be elaborate, it's just to give an idea of what you actually want to achieve. — Anjan Biswas, Nov 02 '15 at 23:22

Combining Two SQL Select Queries with Where Clauses

2 Answers2