?
Solved

Help With Left Outer Join

Posted on 2011-10-18
6
Medium Priority
?
1,179 Views
Last Modified: 2012-05-12
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...
0
Comment
Question by:dcrowley_01
6 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 36988004
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 36988012
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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 36988017
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Closing Comment

by:dcrowley_01
ID: 36988301
Perfect momi!
0
 

Author Comment

by:dcrowley_01
ID: 36988387
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
 

Author Comment

by:dcrowley_01
ID: 36988585
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question