Full outer join not displaying rows from right side table

Question

Full outer join not displaying rows from right side table

I have two tables like below.

Budget table

forecast table

when I joined both these queries using full outer join, I am not getting 2 rows from forecast table. so data is mismatching. how to fix this issue.

note

only selecting F.[Forecast $], F.[Forecast Qty] column from forecast table.

below is the query

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-06T22:40:22.387+00:00

I would start with cleaning up the query. I don't know if you can read all those statements in a long line like it was a novel, but I can't.

I'm also curious why you use this fn CONCAT thing, I've never seen that before, but it seems to make the query messier.

And what is the output from the FULL JOIN query?
siva rama krishna reddy tangirala 1 Reputation point

2021-01-09T00:23:43.893+00:00

Hi Erland,

This query is in a view and written by somebody. I applied where condition while testing. anyway you point is correct. I can remove concat.
EchoLiu-MSFT 14,621 Reputation points

2021-01-12T08:07:44.73+00:00

Do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

3 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-01-06T22:40:22.387+00:00

I would start with cleaning up the query. I don't know if you can read all those statements in a long line like it was a novel, but I can't.

I'm also curious why you use this fn CONCAT thing, I've never seen that before, but it seems to make the query messier.

And what is the output from the FULL JOIN query?
siva rama krishna reddy tangirala 1 Reputation point

2021-01-09T00:23:43.893+00:00

Hi Erland,

This query is in a view and written by somebody. I applied where condition while testing. anyway you point is correct. I can remove concat.
EchoLiu-MSFT 14,621 Reputation points

2021-01-12T08:07:44.73+00:00

Do you have any updates？
Please also remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 1

EchoLiu-MSFT 14,621

Hi @siva rama krishna reddy tangirala ,

Welcome to the Microsoft TSQL Q&A Forum！

The description of your problem is very clear, but you did not provide the corresponding table code and data code, we can not do the test and find the problem.

In addition, when you post a question, please provide a minimal example for testing and the output you expect.

Echo

Answer 2

Visakh 211 Volunteer Moderator

Can you please show the output of the FULL JOIN @siva rama krishna reddy tangirala ?
Also I hope this is the actual complete query and you've not left out any part of it like correlated query, filter etc.

Answer 3

This query is in a view and written by somebody. I applied where condition while testing

When you apply a WHERE condition to a full-join query you need to be very careful. If you say

SELECT ...
FROM  A
FULL JOIN B ON ...
WHERE a.col = 23

You have now filtered out all rows where there are only rows from B, since the condition says that you only want rows from a where col has a non-NULL value. If you want to retain the other rows, you need to say

WHERE a.col = 23 OR a.col IS NULL

The fact that the full-join is in a view does not change this. The view is just a stored macro which is pasted into the query.

Share via

Full outer join not displaying rows from right side table

3 answers

Your answer