Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post LEFT JOINning a subquery

    Hello guys,

    I am wondering why Oracle can't LEFT JOIN a subquery.
    Here is the example
    We have employees and job titles which can change within the time.
    We want to get the list of employees with their lastest job title even
    if they have no job title, so let's imagine the following query:
    Code:
    SELECT
      empl.id AS "empl_id"
    , job.title AS "job_title"
    FROM
      employee AS empl
      LEFT JOIN job ON (
        empl.id = job.empl_id
        AND eff_date = SELECT(
          MAX(j.eff_date)
          FROM job j
          WHERE job.empl_id = j.empl_id
        )
      )
    Do you think it's incoherent ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    very coherent, with one unnecessary set of parentheses and on syntactialkly incorrect set of parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post

    Yes you are right, I typed the example quickly
    Code:
    SELECT
      empl.id AS "empl_id"
    , job.title AS "job_title"
    FROM
      employee AS empl
      LEFT JOIN job ON (
        empl.id = job.empl_id
        AND eff_date = (SELECT
            MAX(j.eff_date)
          FROM job j
          WHERE job.empl_id = j.empl_id
        )
      )
    ;
    Actually this query returns me this message in french
    une colonne ne peut être jointe extérieurement à une sous-interrogation
    In english I think I'd translate it like that
    A field cannot be externaly joint to a subquery
    That's why I was confused

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try like this:
    Code:
    SELECT empl.id AS "empl_id"
         , job.title AS "job_title"
      FROM employee AS empl
    LEFT OUTER
      JOIN job AS j
        ON j.empl_id = empl.id
       AND j.eff_date = 
           ( SELECT MAX(eff_date)
               FROM job 
              WHERE empl_id = j.empl_id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    Same error

  6. #6
    Join Date
    Dec 2007
    Posts
    9
    Use an in statement for joins on subqueries. They work alot better
    Last edited by Rino11; 03-21-08 at 23:29.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rino, is this what you mean?
    Code:
    SELECT empl.id AS "empl_id"
         , job.title AS "job_title"
      FROM employee AS empl
    LEFT OUTER
      JOIN job AS j
        ON j.empl_id = empl.id
       AND j.eff_date IN
           ( SELECT MAX(eff_date)
               FROM job 
              WHERE empl_id = j.empl_id )
    if that actually works, i'll be surprised
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2007
    Posts
    9
    Here is what I ment

    SELECT E."empl_id",
    jt."job_title"
    from

    --EMP_TBL
    (SELECT empl.id AS "empl_id"
    FROM employee AS empl) E,

    --JOB_TBL
    (SELECT jj.empl_id,
    jj.title AS "job_title"
    from job jj
    where (jj.empl_id,jj.eff_date) in (select J.empl_id, MAX(j.eff_date)
    FROM job j
    GROUP BY J.empl_id)) jt
    --tbl_joins
    where E."empl_id" = jt.empl_id(+) -- outer join
    Last edited by Rino11; 03-24-08 at 23:08.

  9. #9
    Join Date
    Dec 2007
    Posts
    9
    r937 - Does that help???

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    me? no, it doesn't help me at all

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2007
    Posts
    9
    Does that make more sense ? I know you didn't start this but I thought I would clearify.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no it doesn't

    i mean, why do you do this --

    --EMP_TBL
    (SELECT empl.id AS "empl_id"
    FROM employee AS empl) E,

    when it would be a lot simpler, and easier to understand like this --

    --EMP_TBL
    (SELECT id FROM employee) E,

    and in fact, why does it have to be a subselect at all???

    see, that's why it doesn't help me

    and i don't even wanna look at the other subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    This time I give you true tables from my DB
    because I was obliged to translate each time.
    So let's consider PS_JOB and PS_NAMES with
    key EMPLID

    Code:
    SELECT A.EMPLID
    , B.NAME
    FROM PS_JOB A
      LEFT OUTER JOIN PS_NAMES B
      ON ( A.EMPLID = B.EMPLID
      AND B.EFFDT IN
        (
          SELECT MAX (EFFDT)
          FROM PS_NAMES
          WHERE EMPLID = B.EMPLID
        )
      )
    still gives me the same "ORA-01799" error

  14. #14
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    And I found the original text in english:
    a column may not be outer-joined to a subquery

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    AND B.EFFDT IN
        (
          SELECT MAX(EFFDT)
          FROM PS_NAMES
          WHERE EMPLID = A.EMPLID
        )
    if that doesn't work, then oracle is pants
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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