Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011

    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)
                             FROM jobs
                             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:

           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)
                                                FROM emails
                                                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-26-11 at 23:54. Reason: left out commas

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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
               FROM jobs
              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
               FROM emails
              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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Thanks so much. I'll give that a try today. I had tried and failed a similar variation, but I'll wager my problem was in the placement of the subquery.



    EDIT: Your suggestion worked perfectly. Thanks again!
    Last edited by lemagrag; 10-27-11 at 13:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts