I am trying to write a query that will return the number of times a runner has used a stall at race track for a given distance and class of race and how many times the runner has won from that stall. From what I can gather I am going to need a recursive CTE, but how to structure the query is beyond me.

In my dbase ( I am using mssql 2014 express) I have a master table - RacingMaster it includes these fields

i) Course_FK (which refers to a table called Courses this table holds the names of all the race courses)

ii) DistofRace_FK (which refers to a table called RaceDistances this table holds the all the race distances)

iii) Draw (this column is an int which holds the stall numbers)

iv) Race_Class ( this column is an int which holds the classes of races 1 through 7)

vi)Finishing_Position (this columns is an int which holds the finishing position of the horse)

I wish to pass into the query 3 variables, a CourseName variable a RaceClass variable and a RaceDistance variable.

In my mind, the pseudo code is something like the following

Select where

RacingMaster.CourseFK = CourseName

RacingMaster.Class =RaceClasss

RacingMaster.DistofRace=RaceDistance

Count all occurence for Draw=1

Count all occurences for Draw=1 and Finish_Pos=1

Loop through all records doing so for each Draw #

The result would be a data set that I can then use in my vb.net application.

I have only a small knowledge of MSSQL an no knowledge of recursive ctes.

Would anyone be kind enough to help? Thank you

Some scripts

USE [RacingData]
GO

/****** Object:  Table [dbo].[Courses]    Script Date: 23-Jun-15 7:00:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Courses](
	[PKCourseId] [int] IDENTITY(1,1) NOT NULL,
	[CourseName] [nvarchar](255) NULL,
 CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED 
(
	[PKCourseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Data for the above table  Lingfied Park, Southwell, Wolverhampton, Kempton Park

/****** Object:  Table [dbo].[RaceDistances]    Script Date: 23-Jun-15 7:00:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RaceDistances](
	[PKRaceDistancesId] [int] IDENTITY(1,1) NOT NULL,
	[RaceDistance] [nvarchar](50) NULL,
 CONSTRAINT [PK_RaceDistances] PRIMARY KEY CLUSTERED 
(
	[PKRaceDistancesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Data for the above table 5f, 5f 220y, 6f, 6f 220y

/****** Object:  Table [dbo].[RacingMaster]    Script Date: 23-Jun-15 6:55:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RacingMaster](
	[PKRacingMaster] [int] IDENTITY(1,1) NOT NULL,
	[Finishing_Position] [int] NOT NULL,
	[RM_Draw] [int] NULL,
	[Race_Class] [int] NULL,
	[Dist_Of_Race_FK] [int] NOT NULL,
	[RM_Course_FK] [int] NOT NULL,
	
 CONSTRAINT [PK_RacingMaster] PRIMARY KEY CLUSTERED 
(
	[PKRacingMaster] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RacingMaster]  WITH CHECK ADD  CONSTRAINT [FK_RacingMaster_Courses] FOREIGN KEY([RM_Course_FK])
REFERENCES [dbo].[Courses] ([PKCourseId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RacingMaster] CHECK CONSTRAINT [FK_RacingMaster_Courses]
GO

ALTER TABLE [dbo].[RacingMaster]  WITH CHECK ADD  CONSTRAINT [FK_RacingMaster_DistancesPF] FOREIGN KEY([Dist_Of_Race_FK])
REFERENCES [dbo].[RaceDistances] ([PKRaceDistancesId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Data for the above table.

Column FinishingPosition any int between 1 and 22

Column RM_Draw any int between 1 and 22

Column Race_Class any int between 1 and 7

Thank you for your interest

• Edited by GordonComstock 13 hours 31 minutes ago Response to request by Ronen

please post queries to create the relevant table and to insert some sample data in order to help us reproduce the issue and write a solution.

* In Addison please write what is the result that you expected to get according the sample data which you posed.

A group by looks like all you are after, group them up as

RacingMaster.CourseFK = CourseName (so are you passing in the key or the name itself?)

RacingMaster.Class =RaceClasss

RacingMaster.DistofRace=RaceDistance

Draw

The count will give you the number of occurrences in a specific draw and a count(case when finish pos = 1 then 1 end) will give total first places

Hi, 

I see that you edited the question and you added DDL (queries to create the relevant tables) :-)
, but we still dont have any data to query and you did not posted the result that you want to get :-(

The basic query that you need which take all the data from these three table is simple JOIN, something like this

declare 
	@CourseName nvarchar(255)    =  '',
	@RaceClass int               =  '',
	@RaceDistance nvarchar (50)  =  1
select * 
from RacingMaster M
left join [Courses] C on C.PKCourseId = M.RM_Course_FK
left join [RaceDistances] D on M.Dist_Of_Race_FK = D.PKRaceDistancesId
where
	C.CourseName   = @CourseName and
	M.Race_Class   = @RaceClass and
	D.RaceDistance = @RaceDistance

1. It is not clear to me what is a "runner" in your case. there is no runners table for runner entity.

* data and result that you want to get (not stories but actual result according to the sample data) could help us to understand

basically if the runner related to specific column in one of these tables then you just need to use "group by" the column that represent the runner.

2. again I have no idea what is "stall" in your case in the tables that you posted, but it look like if you add the column that represent this to the group by + you will filter only winning rows, then you will get "how many times the runner has won from that stall"

** Please post the information that we are missing in order to help you, if this did not get you what you need.

Thanks,

To expand on what RyanAB wrote, the query would look something like

Select c.CourseName, m.Race_Class, d.RaceDistance, m.RM_Draw, 
   Count(*) As NumberOfRunners,
   Count(Case When Finishing_Position = 1 Then 1 End) As NumberWhoFinishedFirst
From dbo.RacingMaster m
Inner Join dbo.Courses c On m.RM_Course_FK = c.PKCourseID
Inner Join dbo.RaceDistances d On m.Dist_Of_Race_FK = d.PKRaceDistancesID
Where c.CourseName = 'Belmont'
And d.RaceDistance = '1 mile'
And m.Race_Class = 5
Group By c.CourseName, m.Race_Class, d.RaceDistance, m.RM_Draw
Order By m.RM_Draw;

Tom

• Marked as answer by GordonComstock 9 minutes ago

Thank you to everyone who posted, all the contributions have been useful and helped me learn a lot. Apologies for any ambiguity in the question or supplied data.