Avatar of markej
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

Further to Question on Cleaning up data

In this link I was  trying to create a stored procedure that will allow me to just show one record for each person/team position.

I know need to link this to an additional table (table b)  using a "left outer join" i.e allow me to see where I don't have records in the second table, the key fields are id and teammember_id and whenever I have tried modifying the existing stored procedure it seems to do a cross join i.e showing a row of data in table a for every entry in table b.

The existing query is:

SELECT d.id,d.ClubId, d.TeamId, d.FirstName, d.Surname, d.TeamPosition, d.status, d.Seasonid, c.position, d.showit
FROM (SELECT * , ROW_NUMBER() OVER (PARTITION BY firstname, surname, teamposition
                             ORDER BY ID DESC) AS rn
FROM teammembers
) AS d, Static_Position as c
WHERE d.rn = 1 and (d.ClubId =@clubid ) AND (TeamPosition <> 5)and teamid = @teamid and TeamPosition = c.posid

I need to add a the following field Cost  from the second table and filter on the seasonid value in this table.

The table has 3 fields Teammember_id, Cost and Seasonid


Microsoft SQL Server.NET ProgrammingASP.NET

Avatar of undefined
Last Comment

8/22/2022 - Mon