Outer Join

Leo Torres
Leo Torres used Ask the Experts™
on
I have two tables that are linked by Item, Client, Country

The query below returns a lot of Nulls. There are some records that are in one table and not in the other for both tables.

I still need to know the Item, Client, Country, Description, but it returns Null instead of the info for those fields

Any suggustions, Im not even sure I can post this here please advise if I need to post else where

Thank you!!!
Select AF.* from
(select F.COUNTRY,F.Client,F.ITEM,F.Description
from Actual2 A full outer Join Forecast2 F on (A.Item = F.Item and A.CLIENT = F.Client and A.COUNTRY = F.Country) ) as AF

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
select A.COUNTRY,A.Client,A.ITEM,F.Description
from Actual2 A LEFT  outer Join Forecast2 F on A.Item = F.Item and A.CLIENT = F.Client and A.COUNTRY = F.Country

Author

Commented:
I get the same result as mine I am still missing the Cleints and info from the Forecast table?? I need to display info from both tables even if there's no match.. This only shows Actual table results that match Forecast..
AneeshDatabase Consultant
Top Expert 2009

Commented:
select A.COUNTRY,A.Client,A.ITEM,F.Description
from Actual2 A LEFT  outer Join Forecast2 F on A.Item = F.Item and A.CLIENT = F.Client and A.COUNTRY = F.Country

union

select A.COUNTRY,A.Client,A.ITEM,F.Description
from Actual2 A RIGHT   outer Join Forecast2 F on A.Item = F.Item and A.CLIENT = F.Client and A.COUNTRY = F.Country
Commented:
No that didnt work its stilling leaving out clients from Forecast

I figured it out!
SELECT  combined.Country
      , combined.Client
      , combined.Item
FROM  (SELECT Country, Client, Item FROM Forecast2
       UNION
       SELECT Country, Client, Item FROM Actual2) AS combined
LEFT OUTER JOIN
  Forecast2 F ON (combined.Country = F.Country AND combined.Client = F.client
                 AND combined.Item = F.Item)
LEFT OUTER JOIN Actual2 A
ON      combined.Item = A.ITEM AND combined.Country = A.COUNTRY AND combined.CLIENT = A.CLIENT

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial