?
Solved

How to do a full outer join with two queries

Posted on 2007-08-09
4
Medium Priority
?
503 Views
Last Modified: 2013-11-24
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


0
Comment
Question by:LeeHopkins
  • 2
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
srafi78 earned 500 total points
ID: 19664950
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
(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')V1 FULL OUTER JOIN
(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')V2
ON
V1.pn = V2.pn
ORDER BY 1
0
 

Author Comment

by:LeeHopkins
ID: 19665050
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
0
 
LVL 8

Expert Comment

by:srafi78
ID: 19665135
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
0
 

Author Comment

by:LeeHopkins
ID: 19665163
I had a cramp I did something stupid
select ..... from
(.....) a,
(.....)b
where a.pn = b.pn
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

862 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