Outer join with inner joins

The simple query below returns three records:

SELECT  bs.po_nbr as TransAcctg_POS            
FROM     bhrv_invoice bi
INNER JOIN bhrv_shpmt bs on bi.bhrv_invoice_id = bs.bhrv_invoice_id
WHERE  bi.load_id = 73160383
 
The return ID's are:
1
2
3


The second simple query returns two records:

select fs.po_nbr  as Transportation_POS
from transportation:frt_shipment_load fsl
inner join transportation:freight_shipment fs on fs.shipment_id = fsl.shipment_id
where fsl.load_id = 73160383  


The return ID's are:
2
4


I want to combine these queries to show me records from both queries that do not match each other.  So, I want to see the results set 1, 3, 4

I'm guessing maybe an outer join should be used, but I'm not sure how to splice this with the two sets of inner joins.  Any ideas?

Lulu0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lulu0Author Commented:
FYI:  Joining both queries with an inner join would look like this:

select bs.po_nbr as Trans_Acctg_POS, fs.po_nbr as Transportation_POS
from bhrv_invoice bi inner join bhrv_shpmt bs on bi.bhrv_invoice_id = bs.bhrv_invoice_id
inner join transportation:frt_shipment_load fsl on fsl.load_id = bi.load_id
inner join transportation:freight_shipment fs on fs.shipment_id = fsl.shipment_id
where bi.load_id = 73160383
0
appariCommented:
try like this
Select *
From (
SELECT  bs.po_nbr as TransAcctg_POS            
FROM     bhrv_invoice bi 
INNER JOIN bhrv_shpmt bs on bi.bhrv_invoice_id = bs.bhrv_invoice_id
WHERE  bi.load_id = 73160383 ) A
Full Outer Join 
(select fs.po_nbr  as Transportation_POS
from transportation:frt_shipment_load fsl 
inner join transportation:freight_shipment fs on fs.shipment_id = fsl.shipment_id
where fsl.load_id = 73160383  )
ON A.TransAcctg_POS = B.Transportation_POS
where A.TransAcctg_POS is null or  B.Transportation_POS is null

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naveen KumarProduction Manager / Application Support ManagerCommented:
also the below can work with union / minus. Give it a try :

select * from
(
SELECT  bs.po_nbr as TransAcctg_POS            
FROM     bhrv_invoice bi
INNER JOIN bhrv_shpmt bs on bi.bhrv_invoice_id = bs.bhrv_invoice_id
WHERE  bi.load_id = 73160383
minus  
select fs.po_nbr  as Transportation_POS
from transportation:frt_shipment_load fsl
inner join transportation:freight_shipment fs on fs.shipment_id = fsl.shipment_id
where fsl.load_id = 73160383   )
union
(
select fs.po_nbr  as Transportation_POS
from transportation:frt_shipment_load fsl
inner join transportation:freight_shipment fs on fs.shipment_id = fsl.shipment_id
where fsl.load_id = 73160383  
minus
SELECT  bs.po_nbr as TransAcctg_POS            
FROM     bhrv_invoice bi
INNER JOIN bhrv_shpmt bs on bi.bhrv_invoice_id = bs.bhrv_invoice_id
WHERE  bi.load_id = 73160383 )
0
Lulu0Author Commented:
nav - your query looks really really cool.  Unfortunately, my informix version does not recognize the minus keyword =(
0
Lulu0Author Commented:
Thanks a bunch guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.