Query problem

Forum Moderators: open

Message Too Old, No Replies

Query problem

well more like my problem

mooger35

5:58 pm on Apr 30, 2007 (gmt 0)

I'm trying to clean up my database by removing a table that I shouldn't need anymore.

What I want is to pull stats from just my one table.

Table 1 (Players)
- playerid
- firstname
- lastname

Table 2 (goalinfo)
- goalid
- season
- teamid
- goal
- assist1
- assist2
- time

What I would like to do is join goal, assist1 and assist2 to playerid so I can pull stats for each player (group by playerid?) WHERE season = 2007.

I can't seem to get my head around the joins.

Can someone give me a hand?

Thanks!

joelgreen

7:18 pm on Apr 30, 2007 (gmt 0)

there should be one more table for teams, like

team
--------
teamid
playerid
name

Select woudl look like

select * from players left join team
on players.playerid = team.playerid
left join goalinfo
on team.teamid = goalinfo.teamid
WHERE season = 2007

mooger35

7:26 pm on Apr 30, 2007 (gmt 0)

there is another team table but I'm just trying to simplify it as I already know how to pull the teamname and playernames from the respective tables.

Sorry maybe I wasn't clear...

Just take out team and time as they are irrelevant.

Basically I'm inserting playerid into goal, assist1 and assist2.

What I'd like to do is count the number of occurences to calculate seasonal totals.

eelixduppy

2:32 am on May 3, 2007 (gmt 0)

moving over to database :)

bmcgee

3:23 am on May 3, 2007 (gmt 0)

select
playerid
, firstname
, lastname
, (select count(goalid) from GoalInfo where GoalInfo.goal = Players.playerid) As NumGoals
, (select count(goalid) from GoalInfo where GoalInfo.assist1 = Players.playerid) As NumAssist1
, (select count(goalid) from GoalInfo where GoalInfo.assist1 = Players.playerid) As NumAssist2
from Players

Query problem

well more like my problem

mooger35

joelgreen

mooger35

eelixduppy

bmcgee

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week