July 11, 2008 at 5:12 am
OK, I have an issue I need help with and so far this site has been remarkable for the help you have all give me. I am trying to expand my fantasy baseball site to SQL as a learning tool and have stumbled.
I have 2 tables that are involved in trades.
trades and trade_details
trades: ID, timestamp, notes (used for player to be named later or other non identifyable resources.
trade_details: ID, trade_ID (same as trades.ID), from_team, player_ID, pick_ID, to_team
first I dont know if it is wise to house two different resources in the same table as I am not sure how to identify each later.
second I am not sure once this is populated how to do a search on lets say a given team to get only trades back on that team. Issue being it would have to be an initial distinct query for trade_ID where a team is found but then would need to display and group the trade complete in its entirity
EXAMPLE:
Lets say the dodgers had made a trade with the reds. in the database it would apear like this. I am subbing names for IDs so that it is easier to understand.
Trades:
23, 7/10/08, Dodgers will also recieve a player to be named later
Trade_Details:
1, 23, Dodgers, Jeff Kent, null, Reds
2, 23, Dodgers, null, 2010 first round pick, Reds
3, 23, Reds, Adam Dunn, null, Dodgers
Once a team is selected on the website I want it to look at the team name retrieve the trade_ID and then group and display the entire results.
trades.ID trades.timestamp
trade_details.team_from, player, pick, team_to
trade_details.team_from, player, pick, team_to
trade_details.team_from, player, pick, team_to
trade_details.team_from, player, pick, team_to
trades.notes
Sorry so long but I am new to this and am pretty lost. Anything past a simple querry gets me pretty darn lost.
July 11, 2008 at 7:34 am
I think I would do the second table more like a ledger.
create table Trade_Details (
DetailID int identity primary key,
DetailDate datetime not null default(getdate()),
TradeID not null references dbo.Trades(TradeID),
TeamID int not null references dbo.Teams(TeamID),
PlayerID int null references dbo.Players(PlayerID),
PickID int null references dbo.Picks(PickID),
constraint CK_PlayerPick check
(PlayerID is not null and PickID is null
or
PlayerID is null and PickID is not null),
Direction char(1),
constraint CK_Direction check
(Direction in ('+','-'))
(The Direction column is simply due to the fact that players and picks can't be positive or negative, like money in a ledger can be.)
Then, if you have a three-part trade:
Joe comes from Team A to Team B
Pick1 goes to Team A
Bob goes to Team A from Team B, replacing Pick1
All of that goes under one TradeID, with entries for Joe as a minus for Team A and a plus for Team B (two separate rows), Pick1 as a plus for Team A (might want to do that as a minus for Team B, up to you), then finally Bob as a plus for Team A and a minus for Team B.
You can reconcile it by making sure that the number of plusses and minuses is equal for any given TradeID, you can query it pretty easily, etc.
Would that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 8:43 am
When trades are done they typicaly are not even
team a might give team b 2 players for one pick
or any combination
I have a player table that has ID's for all the players
I also have a second table for draft picks
So each column in my table is to refernect those tables.
July 14, 2008 at 8:40 am
That would still work with a ledger type structure. It's just an easier data structure to deal with, and it will do what you need on that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 9:16 am
Im confused,
I dont understand how I would know what team a player or pick was traded to? What if it is a three team transaction? I also dont see how I can do a search on a team to find only trade ID's where that team has a transaction and then report the full contence of the trade. I have 16 teams trades being reported in this table. This table serves only as a trade history. Maybe I dont understand the ledger correctly.
July 14, 2008 at 9:34 am
To find which team a player went to, you can query all trade details with that player ID, or you can join between details with + an - in the direction column and the same player ID.
To find all trades where a certain team was involved, select on the team ID, then return the whole trade ID.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 9:43 am
Ok but if
team a trades kenny to team b
team b trades jack to team c
and team c trades ben to team a
how will know who was traded to who?
July 14, 2008 at 9:57 am
All of those go under one trade ID in the details table.
Let's say it's trade ID = 1.
select TeamID, PlayerID, Direction -- Raw data
from dbo.TradeDetails
where TradeID = 1
order by DetailDate
or
select Team, isnull(Player, Pick) as [Player/Pick], Direction, DetailDate as Traded
from dbo.TradeDetails
inner join dbo.Teams
on TradeDetails.TeamID = Teams.TeamID
left outer join dbo.Players
on TradeDetails.PlayerID = Players.PlayerID
left outer join dbo.Picks
on TradeDetails.PickID = Picks.PickID
where TradeID = 1
order by DetailDate
team a trades kenny to team b
team b trades jack to team c
and team c trades ben to team a
Team A will have an entry with Kenny as a minus (in direction)
Team B will have an entry with Kenny as a plus
Team B will have a minus for Jack
Team C will have a plus for Jack
Team C will have a minus for Ben
Team A will have a plus for Ben
All six rows will have TradeID 1. They will have dates on the rows, and so on.
A final query might look like:
select TradeID, Team, isnull(Player, Pick) as [Player/Pick], Direction,
DetailDate as Traded
from dbo.TradeDetails
inner join dbo.Teams
on TradeDetails.TeamID = Teams.TeamID
left outer join dbo.Players
on TradeDetails.PlayerID = Players.PlayerID
left outer join dbo.Picks
on TradeDetails.PickID = Picks.PickID
where TradeID in
(select TradeID
from dbo.TradeDetails
where TeamID = @TeamID_in -- input parameter
order by TradeID, DetailDate
That would give you all the trades that a particular team was involved in, based on an input parameter called "@TeamID_in". You could easily substitute PlayerID = @PlayerID_in and get all the trades, including teams to-and-from, for a particular player.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 11:05 am
Ok im catching on now but I am trying to create the table using your query and I am getting the following
Incorrect syntax near ')'.
in the last line. I am not familiar with the direction type. What am I doint wrong.
July 14, 2008 at 11:22 am
"Direction" isn't a type. It's a column with type Char(1), and a constraint to make sure it's only either "+", or "-".
It looks like I missed a close-parentheses at the end of the table definition, and the data type for TradeID needs to be added (I'm assuming Int).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply