Query Help
-
I am trying to create a query that will return a result set of "Programs" that a "Player" and "Member" can register for WHERE the player and/or member are not already signed up for.
The below query will generate a result set, however records 1 & 5 should not be returned as they are already registered.
I am having difficulty with setting up an additional filter to weed out players and members that are already registered. Any help with getting the query to work would be appreciated. A database diagram for the tables to be used is also included.
SELECT
Players.PlayerFirstName,
Programs.ProgramID,
Programs.ProgramName,
Programs.ProgramDivision,
Programs.ProgramStartDate,
Programs.ProgramPrice,
Programs.ProgramLateFeeDate,
Programs.ProgramLateFee,
Players.PlayerFamilyID,
Players.Playerid
FROM Players
INNER JOIN Programs
ON Players.PlayerDateOfBirth >= Programs.ProgramMinAge AND Players.PlayerDateOfBirth <= Programs.ProgramMaxAge
WHERE (Players.PlayerFamilyID = 'a8ebb328-e4b5-46f0-80fb-9288e0966b98') AND ProgramIsOpen = 'True' AND PlayerBirthCertificateOnFile = 'True'
UNION
SELECT
Members.MemberFirstName,
Programs.ProgramID,
Programs.ProgramName,
Programs.ProgramDivision,
Programs.ProgramStartDate,
Programs.ProgramPrice,
Programs.ProgramLateFeeDate,
Programs.ProgramLateFee,
Members.MemberFamilyID,
Members.MemberID
FROM Members
INNER JOIN Programs
ON Members.MemberDateOfBirth >= Programs.ProgramMinAge
WHERE (Members.MemberFamilyID = 'a8ebb328-e4b5-46f0-80fb-9288e0966b98') AND ProgramIsOpen = 'True'
-
Quick thought, and bear with me if I sound slow :-p
1) how can you compare a date of birth to min or max age, doesn't sound right?
2) What is the role of the PlayerPrograms, technically it's a many to many but it's omitted in the query?
3) last but not least, can you provide some DDL and consumable sample date in the form of an insert statement, makes it a lot easier to answer the question?
😎
-
1) the comparison of birthdate to min/max age determines what program the player is eligible for.
2)PlayerPrograms is the data table for players that have registered for a particular program.
2a)MemberPrograms is the data table for members that have registered for a particular program.
These are the two tables that I am having difficulty with in creating a filter to exclude them from the results.
3) I have attached a zip file to create the table structure and populate each table with some data.
Again, the original query produces a list of "programs" that a "player" and or "member" from a particular family are eligible to register for. The result set needs to exclude players or members that have already signed up for a given program ("playerprograms" and "memberprograms")
-
Again, the original query produces a list of "programs" that a "player" and or "member" from a particular family are eligible to register for. The result set needs to exclude players or members that have already signed up for a given program.
Isn't it as simple as taking the Eligible list and subtracting the "already signed up" list?
SELECT e.ProgramID, e.PlayerIDFROM eligible e
WHERE NOT EXISTS (SELECT 1 FROM Membership WHERE ProgramID = e.ProgramID AND PlayerID = e.PlayerID)
-
I was able to create a stored procedure creating 2 temp tables, successively filtering the data until the final result set is obtained. Is there a more efficient way of accomplishing this, perhaps with sub-queries? The stored procedure is listed below and works fine.
(
@PlayerFamilyID UNIQUEIDENTIFIER,
@MemberFamilyID UNIQUEIDENTIFIER
)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE #AllPrograms
(
PlayerFirstName VARCHAR(50),
ProgramID INTEGER,
ProgramName VARCHAR(50),
ProgramDivision VARCHAR(50),
ProgramStartDate DATE,
ProgramPrice MONEY,
ProgramLateFeeDate DATE,
ProgramLateFee MONEY,
PlayerFamilyID UNIQUEIDENTIFIER,
Playerid INTEGER
)
INSERT INTO #AllPrograms
SELECT
Players.PlayerFirstName,
Programs.ProgramID,
Programs.ProgramName,
Programs.ProgramDivision,
Programs.ProgramStartDate,
Programs.ProgramPrice,
Programs.ProgramLateFeeDate,
Programs.ProgramLateFee,
Players.PlayerFamilyID,
Players.Playerid
FROM Players
INNER JOIN Programs
ON Players.PlayerDateOfBirth >= Programs.ProgramMinAge AND Players.PlayerDateOfBirth <= Programs.ProgramMaxAge
WHERE (Players.PlayerFamilyID = @PlayerFamilyID) AND ProgramIsOpen = 'True' AND PlayerBirthCertificateOnFile = 'True'
UNION
SELECT
Members.MemberFirstName,
Programs.ProgramID,
Programs.ProgramName,
Programs.ProgramDivision,
Programs.ProgramStartDate,
Programs.ProgramPrice,
Programs.ProgramLateFeeDate,
Programs.ProgramLateFee,
Members.MemberFamilyID,
Members.MemberID
FROM Members
INNER JOIN Programs
ON Members.MemberDateOfBirth >= Programs.ProgramMinAge
WHERE (Members.MemberFamilyID = @MemberFamilyID) AND ProgramIsOpen = 'True'
--SELECT * FROM #AllPrograms
CREATE TABLE #AllProgramsMinusPlayers
(
PlayerFirstName VARCHAR(50),
ProgramID INTEGER,
ProgramName VARCHAR(50),
ProgramDivision VARCHAR(50),
ProgramStartDate DATE,
ProgramPrice MONEY,
ProgramLateFeeDate DATE,
ProgramLateFee MONEY,
PlayerFamilyID UNIQUEIDENTIFIER,
Playerid INTEGER
)
INSERT INTO #AllProgramsMinusPlayers
SELECT *
FROM #AllPrograms as e
WHERE NOT EXISTS
(SELECT 1
FROM PlayerPrograms
WHERE PlayerPrograms.PlayerProgram_ProgramID = e.ProgramID
AND
PlayerPrograms.PlayerProgramPlayerID = e.PlayerID)
--SELECT * FROM #AllProgramsMinusPlayers
CREATE TABLE #AllProgramsMinusPlayersAndMembers
(
PlayerFirstName VARCHAR(50),
ProgramID INTEGER,
ProgramName VARCHAR(50),
ProgramDivision VARCHAR(50),
ProgramStartDate DATE,
ProgramPrice MONEY,
ProgramLateFeeDate DATE,
ProgramLateFee MONEY,
PlayerFamilyID UNIQUEIDENTIFIER,
Playerid INTEGER
)
INSERT INTO #AllProgramsMinusPlayersAndMembers
SELECT *
FROM #AllProgramsMinusPlayers as e
WHERE NOT EXISTS
(SELECT 1
FROM MemberPrograms
WHERE MemberPrograms.MemberProgram_ProgramID = e.ProgramID
AND
MemberPrograms.MemberProgramMemberID = e.PlayerID)
SELECT * FROM #AllProgramsMinusPlayersAndMembers
-
How do you define a member as opposed to a player? What's familyID in both tables?
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
A member is a manager, coach, parent, while a player is a child. The two tables contain distinct information.
FamilyID is a UNIQUEIDENTIFIER that is common to the entire family.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply