Re: Problem using left join in query

Hello Paul,

The syntax is incorrect.

It should be:

SELECT JOBS.JOB_ID, STAGES.STAGE
 FROM JOBS left join JOB_DETAIL on(jobs.job_id=job_detail.job_id)

                       left join STAGES
on(stages.stage_id=job_detail.stage_id)
                      AND JOBS.JOB_ID IN (00001)

What happens here, JOBS are the preserved table which is joined with JOB_DETAIL and non-matching rows from JOB_DETAIL are replaced with nulls. Then the result of the first join is the preserved table and it's joined with STAGES, non-matching rows from STAGES being replaced with nulls. During the first join only those rows from JOBS where job_id=0001 are joined.

I am not sure if that's what you want though. It would be helplful, if you posted your 'old style' query.

Rgds.

VC

"Paul Tomlinson" <rubberducky703_at_hotmail.com> wrote in message news:bs6js3$a4tr6$1_at_ID-116287.news.uni-berlin.de...
> All,
>
> I am an experienced oracle query writer, however when I want to join to
> tables I have exclusively only used the standard = operator or the (+)
outer
> join operator.
>
> I need to join table a to table b and table a to table c where a record in
> table a might not exist.....
>
> SELECT JOBS.JOB_ID, STAGES.STAGE
> FROM JOBS, JOB_DETAIL, STAGES
> LEFT JOIN JOBS ON (JOBS.JOB_ID = JOB_DETAIL.JOB_ID)
> LEFT JOIN STAGES ON (STAGES.STAGE_ID = JOB_DETAIL.STAGE_ID)
> AND JOBS.JOB_ID IN (00001)
>
> The row in job_detail might not exist, in which case all I want back is a
> job_id.
>
> There seems to be a problem with my syntax because Oracle is complaining
> that job_details.job_id is an "invalid identifier"
>
> Your thoughts?
> PT
>
>
Received on Mon Dec 22 2003 - 05:58:20 CST