Link to home
Start Free TrialLog in
Avatar of LeeHopkins
LeeHopkinsFlag for United States of America

asked on

How to do a full outer join with two queries

I have a problem I have two queries, I need to do a full outer join between the two querys
where the PN = PN i created a view of each query and it did the join and it works great, but my problem is that the parameters are changed for each query. so I cant use views in my appliation.
here is my view that works
SELECT     V1.bussunit, V1.family, V1.pn, V1.descrip, V1.com_code,
Case when v2.stdcost is Null then 0 else cast(round(V2.stdcost,3) as numeric(10,3)) end  AS PrevVal,
Case when v1.stdcost is null then 0 else cast(round(V1.stdcost,3) as numeric(10,3)) end AS currval, V1.OHqty
FROM         V1 full outer join
                      V2 ON V1.pn = V2.pn

here are my two queries

(V1)
Select bussunit, family, pn, descrip, com_code,
'' as Prevstd, stdcost, '' as variance, OHqty, '' OHReval
from onhand_stdcosthist
where family = 'ABB'
and lastdownloaded = '04/02/2007'
order by 1

(v2)
Select bussunit, family, pn, descrip, com_code,
'' as Prevstd, stdcost, '' as variance, OHqty, '' OHReval
from onhand_stdcosthist
where family = 'ABB'
and lastdownloaded = '05/29/2007'
order by 1


ASKER CERTIFIED SOLUTION
Avatar of srafi78
srafi78
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LeeHopkins

ASKER

I am trying this however when there is an instance of v1 or v2 does not have a match then i am missing  some data.
unlike the full outter join will leave a null value for me in the missing field
I ran the following query as test, I do no know why you are getting the problem

Select a.col1, b.col1, a.col2, b.col2 from
(Select * from (select 1 as col1, 'a' as col2
Union
Select 2, 'b'
Union
Select 3, 'c')x) a FULL OUTER JOIN
(Select * from (select 2 as col1, 'a' as col2
Union
Select 3, 'b'
Union
Select 4, 'c')y) b on a.col1 = b.col1
I had a cramp I did something stupid
select ..... from
(.....) a,
(.....)b
where a.pn = b.pn