delete column from one table created while joining two tables using inner join

Question

I have two temptables joined by using innerjoin and code as follows

IF OBJECT_ID('tempdb..#temp_table3') IS NOT NULL
    DROP TABLE #temp_table3

select VendorNumber,stuff( (select distinct ','+dbo.vendordata.InvoiceStatus
                               from dbo.vendordata
                               where dbo.vendordata.VendorNumber = dbo.vendordata.VendorNumber 
                               for xml path('')
                              ), 1, 1, ''
                            ) as InvoiceStatus
into #temp_table3
from dbo.vendordata
group by VendorNumber


IF OBJECT_ID('tempdb..#temp_table4') IS NOT NULL
    DROP TABLE #temp_table4

select VendorNumber, sum(EY_AmountIncl_LC) AmountIncl_LC,
       sum(EY_AmountExcl_LC) AmountExcl_LC, max(EY_datedocumented) Datedocumented
into #temp_table4
from dbo.vendordata
group by VendorNumber

select * 
from #temp_table3 T inner join 
     #temp_table4 V 
     on T.vendorNumber = V.VendorNumber

Now output is as follows

VendorNumber  Invoicestatus      VendorNumber   EY_AmountIncl   EY_AmountExcl     EY_datedocumented
50000471     V-Parked S-Noted     50000471        281072           281072           00:00.0
5200991      V-Parked S-Noted     5200991         80000            80000            00:00.0
5200595      V-Parked S-Noted     5200595         72401.6         72401.6           00:00.0

How to remove the vendor number which is repeated twice can any one help?

This seams not to be valid MySQL code.. `for xml path('')` and `OBJECT_ID` and `#` are SQL Server (MSSQL).. It seams you have tagged the wrong database here. — Raymond Nijland, Sep 11 '18 at 12:30

score 2 · Accepted Answer · edited Sep 11 '18 at 12:47

2

in the last select

Insetead of select * from ....

select only the columns you need eg:

select T.VendorNumber, T.InvoiceStatus, V.AmountIncl_LC, 
       V.AmountExcl_LC, V.Datedocumented
from #temp_table3 T inner join 
     #temp_table4 V 
     on T.vendorNumber = V.VendorNumber

edited Sep 11 '18 at 12:47

Yogesh Sharma

49,870
5
26
52

answered Sep 11 '18 at 12:32

ScaisEdge

131,976
10
91
107

delete column from one table created while joining two tables using inner join

1 Answers1