Troubleshooting full outer join
-
Hi
I have a view with the turnovers. Fields are Year, Month, Client, Turnover.
I have a second view with the costs. Fields are Year, Month, Client, Costs.
Now I have created a view combining these two.
select turnover.customer, turnover.year, turnover.month, turnover.turnover, costs.costs
from turnover full outer join costs
on turnover.client = costs.client and turnover.year = costs.year and turnover.month = costs.month
The results I should get, I guess, are all records from turnover and all records from costs. Well, I receive all records from turnover (so even if costs is null) but not all records from costs (those where turnover is null). How come ?
Haven't got a clue.
Thx J
JV -
You should really look at the data because you are supposed to get the matches and the missmatches too
BTW : your select has a "customer" column not identified in any of the definitions
* Noel -
Try:
SELECT COALESCE(turnover.client,costs.client) AS Client
, COALESCE(turnover.[year],costs.[year]) AS [Year]
, COALESCE(turnover.[month], costs.[month]) AS [Month]
, turnover.turnover, costs.costs
FROM turnover
FULL OUTER JOIN costs ON turnover.client = costs.client
AND turnover.[year] = costs.[year]
AND turnover.[month] = costs.[month]
Andy
-
Noeld
Sorry customer is same as client. This is not my real qquery but simple translation. Andy thx, I will giev it shot. Learned again !
Thx
J
JV -
David
Much obliged, it works super !!
I still need to learn a lot.
Thx J
JV -
If that (David's) query worked yours returned all the records!! Contrary to what you said!
The only difference was in the column population not on missing records
* Noel -
Nope, sorry.
Strangely enough it didn't show all records. Now it does.
JV -
Well, you tell me if I am blind but:
Your query record source:
from
turnover full outer join costs
on turnover.client = costs.client
and turnover.year = costs.year
and turnover.month = costs.month
and David's record source
FROM turnover FULL OUTER JOIN costs
ON turnover.client = costs.client
AND turnover.[year] = costs.[year]
AND turnover.[month] = costs.[month]
look IDENTICAL to me
and that is what determines the # of records you get back!!!
* Noel -
Noel,
Since this is a FULL OUTER JOIN the problem was in:
SELECT turnover.client
, turnover.[year]
, turnover.[month]
, turnover.turnover, costs.costs
Caused the costs fields not being included in the output, so using COALESCE caused both to be included when it was their time to be NULL.
SELECT COALESCE(turnover.client,costs.client) AS Client
, COALESCE(turnover.[year],costs.[year]) AS [Year]
, COALESCE(turnover.[month], costs.[month]) AS [Month]
, turnover.turnover, costs.costs
Andy
-
Thx Andy
Indeed the result was different, and I have now the one I need.
JV
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply