If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > LEFT JOINning a subquery

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 ?
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
very coherent, with one unnecessary set of parentheses and on syntactialkly incorrect set of parentheses
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Quote:
une colonne ne peut être jointe extérieurement à une sous-interrogation
In english I think I'd translate it like that
Quote:
A field cannot be externaly joint to a subquery
That's why I was confused
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
Same error
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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 22:29.
Reply With Quote
  #7 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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 22:08.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 9
r937 - Does that help???
Reply With Quote
  #10 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
me? no, it doesn't help me at all

__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Dec 2007
Posts: 9
Does that make more sense ? I know you didn't start this but I thought I would clearify.
Reply With Quote
  #12 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Oct 2004
Location: Paris, FRANCE
Posts: 132
And I found the original text in english:
Quote:
a column may not be outer-joined to a subquery
Reply With Quote
  #15 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On