• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

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

Help!

Mark
0
markej
Asked:
markej
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do

SELECT d.id,d.ClubId, d.TeamId, d.FirstName, d.Surname, d.TeamPosition, d.status, d.Seasonid, c.position, d.showit
, t.cost
FROM (SELECT * , ROW_NUMBER() OVER (PARTITION BY firstname, surname, teamposition
                             ORDER BY ID DESC) AS rn
FROM teammembers
) AS d
join Static_Position as c
on 1=1
,left join table3 t
on t.teammemberid = d.id
and t.seasonid = d.seasonid

WHERE d.rn = 1 and (d.ClubId =@clubid ) AND (TeamPosition <> 5)and teamid = @teamid and TeamPosition = c.posid
0
 
markejAuthor Commented:
Hi When I try that I'm getting the following error "Incorrect syntax near the keyword 'left'."

Also I've discovered that the seasonid has not been fully populated can I use in the where clause t.seasonid = @seasonid and remove "and t.seasonid = d.seasonid" ?
0
 
Olaf DoschkeSoftware DeveloperCommented:
remove the comma before left.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree

SELECT d.id,d.ClubId, d.TeamId, d.FirstName, d.Surname, d.TeamPosition, d.status, d.Seasonid, c.position, d.showit
, t.cost
FROM (SELECT * , ROW_NUMBER() OVER (PARTITION BY firstname, surname, teamposition
                             ORDER BY ID DESC) AS rn
FROM teammembers
) AS d
join Static_Position as c
on 1=1
Left join table3 t
on t.teammemberid = d.id


WHERE d.rn = 1 and (d.ClubId =@clubid ) AND (TeamPosition <> 5)and teamid = @teamid and TeamPosition = c.posid
0
 
markejAuthor Commented:
Thank you gents definitely saved me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now