Unanswered: Complex inner join followed by left outer join
Hi. I am new to this site and I promise I have spent a fair amount of time searching for an answer. I've stumbled upon a few posts that get me close, but I'm just not getting the results I'm hoping to achieve. I can't post my exact query because of a confidentiality agreement, but I shall attempt to provide a pseudo-query below. Any input would be appreciated.
FYI, I am dealing with a very complex (and sometimes poorly written) Oracle 11g database and only have READ access.
SELECT s.user_ID AS ID,
c.payroll_class AS CLASS,
j.job_date AS DATE
FROM super s, classes c, jobs j
WHERE s.user_ID = c.user_ID
AND c.user_job = j.job_ID
AND j.job_date = (SELECT MAX(job_date)
WHERE j.job_ID = job_ID
AND job_date <= sysdate)
The above represents the table to which I'd like to LEFT OUTER JOIN the following:
SELECT e.EMAIL_ADDRESS as EMAIL
FROM emails e
WHERE s.USER_ID = e.EMAIL_ID
AND e.EMAIL_TYPE = 'M'
AND e.EMAIL_UPDATE_DATE = (SELECT MAX(email_update_date)
WHERE e.email_ID = email_ID
AND email_update_date <= sysdate)
There are a lot more conditions to the first query, but the main obstacles I encounter are:
a) I can't outer join to a column when there are subqueries (e.g. the max date selections)
b) By using a WHERE clause in the first SELECT statement, Oracle is telling me that I cannot perform a subsequent outer join because I am not ending my statement correctly.
I can give a more concrete example of the latter the next time I have my work laptop with me, but in the meantime, does anyone have any input on how I might be able to go about joining my emails to my inner joined query when such emails exist fitting the parameters outlined?
Last edited by lemagrag; 10-27-11 at 00:54.
Reason: left out commas
SELECT s.user_id AS id
, c.payroll_class AS class
, j.job_date AS date
, e.email_address AS email
FROM super s
JOIN classes c
ON c.user_id = s.user_id
JOIN ( SELECT job_id
, MAX(job_date) AS latest_job_date
WHERE job_date <= sysdate
BY job_id ) m1
ON m1.job_id = c.user_job
JOIN jobs j
ON j.job_id = m1.job_id
AND j.job_date = m1.latest_job_date
JOIN ( SELECT email_id
, MAX(email_update_date) AS latest_email_date
WHERE email_type = 'M'
AND email_update_date <= sysdate
BY email_id ) m2
ON m2.email_id = s.user_id
JOIN emails e
ON e.email_id = m2.email_id
AND e.email_type = 'M'
AND e.email_update_date = m2.latest_email_date