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:

result

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.

P.Salmon The problem in inner join is it will eliminate the values will not match. As i explain, i need all data as it is table beause it is quite possible that one product is received but not yet issued so it should be displayed on received side. — Talat Farooq, Dec 12 '20 at 08:36

score 0 · Accepted Answer · answered Dec 12 '20 at 09:41

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.

Results

Fiddle to see it in action.

Sander, First of all thanks very much for explaining the solution in such a explecit way. Solution 3 is the actual solution of my probelm. But while i am using this with where condtion despite having 2 record in Received and 1 record in Issue i am getting only one record from both tables(The record of my tables not created by you). Would you please explain why it is happening and what changes are required to solve this issue. — Talat Farooq, Dec 12 '20 at 11:40
I tried to reproduce your issue (changed some sample data and added `where` clause) and provided a solution in [this fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=70965b54ae5e1c29fcbdca1fe3e113b5). — Sander, Dec 12 '20 at 12:17

Joins tables in SQL with uneven rows without duplication

1 Answers1