Joins tables in SQL with uneven rows without duplication

Question

I have two tables in SQL Server GRV and GIV with these columns:

• GRV : Date, ProductID, ProductName, Unit, ReceivedQTY
• GIV : Date, ProductID, ProductName, Unit, Quantity

Query is as follows:

select 
    GRV.ProductID, GRV.ProductName, GRV.Unit, GRV.ReceivedQTY,
    GIV.ProductID, GIV.ProductName, GIV.Unit, GIV.Quantity
from 
    GRV
full outer join 
    GIV on GRV.ProductID = GIV.ProductID

This is what I am getting:

The problem is the rows of red font are not actually in my GIV table. What I want is only actual data of table should combine as it is. GRV on right side and GIV on left side without even rows indicating null.

Is there any option available? The reason I need this to create a stock ledger Crystal Report where I can show all transactions of received and issue quantity date wise and generate closing balance in the end. Please help me in this regard.

Answer 1

Your question is missing a clear "expected result".

Here are some options that might help you.

Sample data

create table ItemIssued
(
  ProductId nvarchar(5),
  Quantity int
);
insert into ItemIssued (ProductId, Quantity) values
('P0001', 100),
('P0002',  50),
('P0004',   1);

create table ItemReceived
(
  ProductId nvarchar(5),
  Quantity int
);
insert into ItemReceived (ProductId, Quantity) values
('P0002',  55),
('P0003', 200);

Solution 1

With null.

select i.ProductId as ProductId,
       i.Quantity as Quantity,
       r.ProductId as ProductId,
       r.Quantity as Quantity
from ItemIssued i
full join ItemReceived r
  on r.ProductId = i.ProductId;

Solution 2

Without null.

select coalesce(i.ProductId,'') as ProductId,
       coalesce(convert(nvarchar(5), i.Quantity),'') as Quantity,
       coalesce(r.ProductId,'') as ProductId,
       coalesce(convert(nvarchar(5), r.Quantity),'') as Quantity
from ItemIssued i
full join ItemReceived r
  on r.ProductId = i.ProductId;

Solution 3

Tables next to each other.

with ctei as
(
  select row_number() over(order by i.ProductId) as RowNum,
         i.ProductId,
         i.Quantity
  from ItemIssued i
),
cter as
(
  select row_number() over(order by r.ProductId) as RowNum,
         r.ProductId,
         r.Quantity
  from ItemReceived r
)
select ctei.ProductId,
       ctei.Quantity,
       cter.ProductId,
       cter.Quantity
from ctei
full join cter
  on cter.RowNum = ctei.RowNum;

Solution 4

All products with quantities.

with cte as
(
  select i.ProductId
  from ItemIssued i
    union
  select r.ProductId
  from ItemReceived r
)
select c.ProductId,
       i.Quantity,
       r.Quantity
from cte c
left join ItemIssued i
  on i.ProductId = c.ProductId
left join ItemReceived r
  on r.ProductId = c.ProductId;

Results

In the same order as the solutions.

Fiddle to see it in action.