Cross Query Tables

[watoth]

Ok im not sure what it is I need. But I have a sort of fantasy baseball website that I want to add something to. I want to see what players have been on the most championship teams. I just don't know how to query the players against the results of those that have won a world series.

Here are the Tables Im working with

Season Results
id, int
team_id, int
wins, numeric
loss, numeric
year, numeric
division, int
pennant, int
world_series, int

This table will tell me the team_id of any team that has won a world series. world_series will be "1" for any team that has won a world series.

Stats_Batting
ID, int
player_id, int
team_id, int
year, numeric

Now this table has much more than this as I have each stat as well but these are the basic fields that will give us what we need most notably the the player_id, and year.

Now what I need to do is count the number of world series teams a player has played for.
I need to count the number of times that a player_id shows up with the same team_id and and year that match with a season results team_id and year where world series = 1

I hope this makes sense and I have provided enough information.

[BitBlt]

Interesting scenario. I have built a little mini-version of this on my play-database (pardon the pun), but I do have a request for clarification.

Are you interested in counting up the total number of times this player has been on a world series team (regardless of which team)? So, for instance, if Babe Ruth won the world series 4 times with the Yankees and twice with the Red Sox, do you expect to see

Player     WSTeams
Babe Ruth     2

If so, use this:

select a.player_id, a.player_name, COUNT(distinct b.team_id)
from dbo.player a
inner join dbo.stats_batting b
on a.player_id = b.player_id
inner join dbo.seasonresults c
on b.team_id = c.team_id
and b.[year] = c.[year]
and c.world_series = 1
group by a.player_id, a.player_name
having COUNT(c.[year]) > 0
order by COUNT(distinct b.team_id) desc

Or do you wish to see the total number of times he's been on a world series winner?

Player     WSWins
Babe Ruth     6

Then use this:

select a.player_id, a.player_name, COUNT(c.[year])
from dbo.player a
inner join dbo.stats_batting b
on a.player_id = b.player_id
inner join dbo.seasonresults c
on b.team_id = c.team_id
and b.[year] = c.[year]
and c.world_series = 1
group by a.player_id, a.player_name
having COUNT(c.[year]) > 0
order by COUNT(c.[year]) desc

Or do you expect to see

Player      Team     WSWins
Babe Ruth   Yankees  4
Babe Ruth   Red Sox  2

Then use:

select a.player_id, a.player_name, b.team_id, b1.team_name, COUNT(c.[year])
from dbo.player a
inner join dbo.stats_batting b
on a.player_id = b.player_id
inner join dbo.team b1
on b.team_id = b1.team_id
inner join dbo.seasonresults c
on b.team_id = c.team_id
and b.[year] = c.[year]
and c.world_series = 1
group by a.player_id, a.player_name, b.team_id, b1.team_name
having COUNT(c.[year]) > 0
order by COUNT(c.[year]) desc

As you can see, each of these has a slightly different query. I hope one of these was what you were looking for. Good luck!