I'm trying to query two tables to join them on itemnumber. Here are my two tables:
ID --> 123
VendorName --> Amazon
VendorNumber --> 1111
Invoice --> 0001
ID --> 123
GL Code -->
I want to join the two tables on ID - no problem.
However, I want to count the number of rows in table2 (which will reset when ID changes) and display the count along with the join. So the end result will look something like this:
ID1, VendorName1, VendorNumber1, Count(1), GL Code1, GL Amount1
ID1, VendorName1, VendorNumber1, Count(2), GL Code2, GL Amount2
ID2, VendorName2, VendorNumber2, Count(1), GL Code1, GL Amount1
ID2, VendorName2, VendorNumber2, Count(2), GL Code2, GL Amount2
ID2, VendorName2, VendorNumber2, Count(3), GL Code3, GL Amount3
ID3, VendorName3, VendorNumber3, Count(1), GL Code1, GL Amount1
and so forth....
I started with the following query but can't seem to reset the Count when the ID changes.
There might be some syntax errors in the code below since I did not want to paste the query from my production.
I'd appreciate any help. Thanks.
identity(int,1,1) as Count,
table2.GLCode as GLCode,
table2.GLAmount as GLAmount
from table2, table1 where table1.ID = table2.ID
select table1.ID, table1.VendorName, table1.VendorNumber, temp.Count, temp.GLCode, temp.GLAmount
from table1, #myTable as temp
Where table1.ID = temp.ID