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
) 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