Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Joining two left join tables OR two inner join tables

888181Sep 22 2011 — edited Sep 22 2011
Hi, these are two separate queries that I need to condense into one. The common table between them is called Agreement_line. I would like my result to have only 3 columns if possible ie policy_no, roof_construction and lightning_conductor.

Select distinct
al.policy_no
,oh.lightning_conductor
,oh.roof_construction

from obj_house oh
left join agreement_line al
ON al.agr_line_no = oh.agr_line_no
where oh.roof_construction = 'THA'
and oh.lightning_conductor = 'N'
and oh.newest = 'Y'
and al.newest = 'Y'
---------------------------------------------------------------
Select distinct
al.policy_no
,ob.lightning_conductor
,ob.roof_construction

from obj_beach ob
left join agreement_line al
ON al.agr_line_no = ob.agr_line_no
where ob.roof_construction = 'THA'
and ob.lightning_conductor = 'N'
and ob.newest = 'Y'
and al.newest = 'Y'

ALSO I can perform an inner join on the same two queries and still receive the results I want ie

Select
al.policy_no
,oh.lightning_conductor
,oh.roof_construction

from agreement_line al, obj_house oh
where al.agr_line_no = oh.agr_line_no
and oh.roof_construction = 'THA'
and oh.lightning_conductor = 'N'
and oh.newest = 'Y'
and al.newest = 'Y'
---------------------------------------------------------------------------
Select
al.policy_no
,ob.lightning_conductor
,ob.roof_construction

from agreement_line al, obj_beach ob
where al.agr_line_no = op.agr_line_no
and ob.roof_construction = 'THA'
and ob.lightning_conductor = 'N'
and ob.newest = 'Y'
and al.newest = 'Y'

I still have no idea how to join them so that I dont lose any results and only have 3 columns being returned.

Banner:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 20 2011
Added on Sep 22 2011
6 comments
409 views