use cursors and try this....

Hi,

try the below one....

DECLARE details_cursor CURSOR FOR

SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase INNER JOIN sales ON purchase.purchaseitem=sales.saleitem;
UNION ALL
SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase LEFT JOIN sales ON purchase.purchaseitem=sales.saleitem
WHERE (((sales.saleitem) Is Null));
UNION ALL
SELECT purchase.purchaseitem, purchase.purchaseprice, purchase.purchasenumber, sales.saleitem, sales.saleprice, sales.salenumber
FROM purchase RIGHT JOIN sales ON purchase.purchaseitem = sales.saleitem
WHERE (((purchase.purchaseitem) Is Null))

declare @PurchaseName varchar(100)

decalre @PurchaseNo int

declare @PurchasePrice int

declare @SaleName varchar(100)

decalre @SaleNo int

declare @SalePrice int

declare @Count int

create table tempTable (PurchaseName varchar(100), PurchaseNo int, PurchasePrice int, SaleName varchar(100), SaleNo int, SalePrice int)

OPEN details_cursor

FETCH NEXT FROM details_cursor into @PurchaseName, @PurchaseNo, @PurchasePrice, @SaleName, @SaleNo, @SalePrice

insert into tempTable  select @PurchaseName as 'PurchaseName',  @PurchaseNo as 'PurchaseNo', @PurchasePrice as 'PurchasePrice', @SaleName as 'SaleName', @SaleNo as 'SaleNo', @SalePrice  as 'SalePrice'

WHILE @@FETCH_STATUS = 0
BEGIN

     FETCH NEXT FROM details_cursor into @PurchaseName, @PurchaseNo, @PurchasePrice, @SaleName, @SaleNo, @SalePrice

select @Count  = count(*) from temptable where PurchaseName = @PurchaseName and PurchaseNo = @PurchaseNo, PurchasePrice = @PurchasePrice

if(@Count > 0)

begin

          @PurchaseName = NULL

         @PurchaseNo = NULL

         @PurchasePrice = NULL

end

insert into tempTable  select @PurchaseName as 'PurchaseName',  @PurchaseNo as 'PurchaseNo', @PurchasePrice as 'PurchasePrice', @SaleName as 'SaleName', @SaleNo as 'SaleNo', @SalePrice  as 'SalePrice'

END

CLOSE details_cursor

DEALLOCATE details_cursor  

select * from tempTable

drop table tempTable