Help With Left Outer Join

I need help writing a left outer join correctly. Please use my tables in the solution not generic tables. There are three tables involved. id_rec, adm_rec and intvwrecom_rec. There is a one to one relationship between adm_rec and id_rec based on the id field and a one to many relationship between id_rec and intvwrecom_rec based on id.

First table is id_rec, this table has one record per id:
id_rec contains the fields:
id (primary key)
firstname
lastname
middlename

Second table is adm_rec, this table also has one record per id:
adm_rec contains the field:
id (primary key)
common_app_id
plan_enr_yr

intvwrecom_rec is the table that has many rows for each id:
intvwrecom_rec contains the fields:
id
intvwrecom      (examples in this field are REC1, REC2, REC3, INT1)
score
staff_init


So the query should do a normal join based on id between id_rec and adm_rec and a left outer join on intvwrecom_rec where intvwrecom_rec.intvwrecom = 'INT1' and grab the staff_init and the score. Some rows will not have an 'INT1', for those I will just take a blank '' for each result. And that's why I think I need to use a left outer join.

Here is my weak effort that is giving me a syntax error:

select a.common_app_id, i.firstname, i.lastname, i.middlename, NVL(v.score,'') as score from id_rec i, adm_rec a, intvwrecom_rec v
left outer join intvwrecom_rec on i.id = v.id
where i.id = a.id and i.id = v.id and a.plan_enr_yr = '2011' and v.intvwrecom = 'INT1'

My error currently is An On Clause has an invalid table reference...
dcrowley_01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
It's pretty much always a bad idea to mix "old style" joins (with tables that are seperated by commas) and new "new style" joins (with explicit JOIN keywords). For clarity and usefulness, I recommend solely using "new style".

It goes a little something like this ...

select a.common_app_id,
       i.firstname,
       i.lastname,
       i.middlename,
       NVL(v.score,'') as score 
  from id_rec i
  join adm_rec a
    on i.id = a.id
  join intvwrecom_rec v
    on i.id = v.id
  left outer join intvwrecom_rec
    on i.id = v.id
 where a.plan_enr_yr = '2011'
   and v.intvwrecom = 'INT1'

Open in new window

0
Rajkumar GsSoftware EngineerCommented:
Use join or alias - don't mix :)
select a.common_app_id, i.firstname, i.lastname, i.middlename, NVL(v.score,'') as score
 from id_rec i 
inner join adm_rec on a i.id = a.id 
inner join intvwrecom_rec v on i.id = v.id 
left outer join intvwrecom_rec on -- join condition here
where a.plan_enr_yr = '2011' and v.intvwrecom = 'INT1'

Open in new window

0
momi_sabagCommented:
select a.common_app_id, i.firstname, i.lastname, i.middlename, NVL(v.score,'') as score
from id_rec i join adm_rec a on i.id = a.id
 left outer join intvwrecom_rec v on i.id = v.id and v.intvwrecom = 'INT1'
where a.plan_enr_yr = '2011'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dcrowley_01Author Commented:
Perfect momi!
0
dcrowley_01Author Commented:
momi, if I wanted to add a third on to one table called profile_rec what is the syntax?

select p.sex, a.common_app_id, i.firstname, i.lastname, i.middlename, NVL(v.score,'') as score
from id_rec i join adm_rec a join profile_rec p on i.id = a.id = p.id
 left outer join intvwrecom_rec v on i.id = v.id and v.intvwrecom = 'INT1'
where a.plan_enr_yr = '2011'
0
dcrowley_01Author Commented:
I got it:
select p.sex, a.common_app_id, i.firstname, i.lastname, i.middlename, NVL(v.score,'') as score
from id_rec i
join adm_rec a on i.id = a.id
join profile_rec p on i.id = p.id
 left outer join intvwrecom_rec v on i.id = v.id and v.intvwrecom = 'INT1'
where a.plan_enr_yr = '2011'

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.