Amend procedure to display names instead of primary keys

1

The following procedure works

alter PROCEDURE [dbo].[TrainerJockeyAEbyCourseDistanceClass] 
-- Add the parameters for the stored procedure here
@Course int,
@Distance int ,
@RaceClass int

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
WITH Base (RM_Trainer_FK,RM_Jockey_FK,TotalWins) AS
-- Insert statements for procedure here
( SELECT RM1.RM_Trainer_FK,RM1.RM_Jockey_FK, COUNT(*)
FROM RacingMaster AS RM1
join Trainers
on
rm1.RM_Trainer_FK=Trainers.PKTrainerId

join Jockeys
on rm1.RM_Jockey_FK=Jockeys.PKJockeyId
where rm1.Finishing_Position=1
and rm1.RM_Course_FK=@Course
and rm1.Race_Class=@RaceClass
and rm1.Dist_Of_Race_FK=@Distance
GROUP BY RM1.RM_Trainer_FK ,rm1.RM_Jockey_FK)
SELECT RM.RM_Trainer_FK,RM.RM_Jockey_FK,AE= B.TotalWins / 1/(sum(RM.RM_SP+1)) FROM RacingMaster
as RM
LEFT JOIN Base AS B ON RM.RM_Trainer_FK = B.RM_Trainer_FK
left join base as C on rm.RM_Jockey_FK=b.RM_Jockey_FK
where
rm.RM_Course_FK=@Course
and
RM.Race_Class=@RaceClass
and
rm.Dist_Of_Race_FK=@Distance

GROUP BY RM.RM_Trainer_FK,RM.RM_Jockey_FK,RM_Course_FK,RM.Dist_Of_Race_FK ,rm.Race_Class, b.TotalWins
ORDER by RM.RM_Trainer_FK,rm.RM_Jockey_FK ASC;
END

the output it produces is

RM_Trainer_FK RM_Jockey_FK AE
5 59 NULL
7 28 NULL
9 28 NULL
14 25 NULL
16 3 NULL
19 37 0.03571428571428571428571428
27 1444 NULL
29 99 NULL
40 1632 NULL
45 1218 0.00348432055749128919860627
51 54 NULL
51 106 NULL
61 64 NULL
65 13 0.16666666666666666666666666
65 70 0.07142857142857142857142857
65 1535 0.04166666666666666666666666
65 1550 0.01587301587301587301587301
66 18 0.03571428571428571428571428
69 105 NULL

The procedure is based around a table RacingMaster, this table, contains the following fields,
RM_Trainer_FK
RM_Jockey_FK
RM_Course_FK
Dist_Of_Race_FK
These are foreign keys which refer to Trainers, Jockeys, Course, RaceDistances
What I need, but can't achieve, is instead of the output being the Primarykeys of the Trainer and Jockey tables, instead I wish for the TrainerName and JockeyName fields to be displayed.

Table Trainers and table Jockeys each have only 2 fields
1 PKTrainerId which is a primary key identity field
2 TrainerName wich is an nvarchar (30)

Jockey has a similar construction.

Could anyone please amend my procedure so that the names of the trainers and jockeys will be displayed instead of their primary keys? Thank you.

2

Can't you just replace these columns with the ones you want? The ones that contain the names??

3

That is what I have been trying to do, but my knowledge of sql is minimal, and I keep encountering errors such as the column Trainer.TrainerName cannot be bound or, TrainerName is not part of the select statement or the group by clause.

4

It would be Trainers not Trainer (you need the "s"). And you might select is as MAX(Trainers.TrainerName) to avoid having to add it to the GROUP BY. Something like this (of course I can't test it without your tables and data) Hopefully you get the idea.

ALTER PROCEDURE [dbo].[TrainerJockeyAEbyCourseDistanceClass]
	-- Add the parameters for the stored procedure here
	@Course INT
	,@Distance INT
	,@RaceClass INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	WITH Base (
		RM_Trainer_FK
		,RM_Jockey_FK
		,TotalWins
		-- add trainer name
		,TrainerName
		)
	AS
		-- Insert statements for procedure here
		(
		SELECT RM1.RM_Trainer_FK
			,RM1.RM_Jockey_FK
			,COUNT(*)
			-- get trainer Name
			,MAX(Trainers.TrainerName)
		FROM RacingMaster AS RM1
		INNER JOIN Trainers ON rm1.RM_Trainer_FK = Trainers.PKTrainerId
		INNER JOIN Jockeys ON rm1.RM_Jockey_FK = Jockeys.PKJockeyId
		WHERE rm1.Finishing_Position = 1
			AND rm1.RM_Course_FK = @Course
			AND rm1.Race_Class = @RaceClass
			AND rm1.Dist_Of_Race_FK = @Distance
		GROUP BY RM1.RM_Trainer_FK
			,rm1.RM_Jockey_FK
		)
	SELECT RM.RM_Trainer_FK
		,RM.RM_Jockey_FK
		,AE = B.TotalWins / 1 / (sum(RM.RM_SP + 1))
		-- Add Trainer Name
		,MAX(TrainerName)
	FROM RacingMaster AS RM
	LEFT JOIN Base AS B ON RM.RM_Trainer_FK = B.RM_Trainer_FK
	LEFT JOIN base AS C ON rm.RM_Jockey_FK = b.RM_Jockey_FK
	WHERE rm.RM_Course_FK = @Course
		AND RM.Race_Class = @RaceClass
		AND rm.Dist_Of_Race_FK = @Distance
	GROUP BY RM.RM_Trainer_FK
		,RM.RM_Jockey_FK
		,RM_Course_FK
		,RM.Dist_Of_Race_FK
		,rm.Race_Class
		,b.TotalWins
	ORDER BY RM.RM_Trainer_FK
		,rm.RM_Jockey_FK ASC;
END
5

Thank you for your response. Unfortunately when I add TrainerName in the suggested place I get 'ambiguous column name TrainerName' when I supply Trainers.TrainerName the error is, 'Trainers.TrainerName could not be bound.

6

Perhaps you could post the CREATE TABLE statements for all the tables involved. Apparently TrainerName exists in more than one table.

In the meantime, change these parts:

WITH Base (
RM_Trainer_FK
,RM_Jockey_FK
,TotalWins
-- add trainer name
,Trainers.TrainerName
)
...

SELECT RM.RM_Trainer_FK
,RM.RM_Jockey_FK
,AE = B.TotalWins / 1 / (sum(RM.RM_SP + 1))
-- Add Trainer Name
,MAX(Base.TrainerName)

7

And now it works! Thank you so much. I could have sworn that I had tried Base.TrainerName and had the can't be bound error message. I guess that's tiredness for you. Thank you so much for taking the time to solve this for me.