Joining two left join tables OR two inner join tables
888181Sep 22 2011 — edited Sep 22 2011Hi, 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