Jump to content


This topic is now archived and is closed to further replies.


Quick query help

Recommended Posts

I'm really drawing a blank here.. I have two tables:

Columns (postion_id, position_abbv)

Columns (player_id, position_id, mlbteam_id)

I'm trying to build a query to return the position_abbv (QB, RB, WR, etc) for each player...Looks like I need a join, but I just cant figure it out. Any quick help would be appreciated

Share this post

Link to post
Share on other sites
select players.player_id, positions.position_abbv
from players, positions
where players.position_id = positions.position_id

I think...

Share this post

Link to post
Share on other sites
khendar's query will work, but I highly encourage the more formal syntax, as it helps you understand the nature of a join and what a join condition is. Also I used a LEFT JOIN so that if a player has not yet been assigned a position, he shows up in the list with a blank position. The other way, the player would be completely omitted.

[code]SELECT p.player_id, s.position_abbv
FROM players p
LEFT JOIN positions s ON p.position_id = s.position_id
WHERE anythingelseyouwant[/code]

Share this post

Link to post
Share on other sites
I dont know..I still cant get it to work..Maybe I've been staring at my screen too long. Ideally, I would like to add it to this query, which pulls a random player and calculates their fantasy point value
$sql = "SELECT lname, fname, SUM(pass_yd) AS sumpassyd, SUM(pass_att) as sumpassatt, SUM(pass_cmp) as sumpasscmp, SUM(rush_yd) AS sumrushyd, SUM(rush_td) AS sumrushtd, SUM(rush_att) AS sumrushatt, SUM(rec_yd) AS sumrecyd, SUM(rec_td) AS sumrectd, SUM(rec_att) AS sumrecatt,SUM(pass_td) as sumpasstd, SUM(playerstats.int) AS sumqbint, SUM(fmbl_lost) as sumfmbl,
SUM(twopt) AS sumtwopt, SUM(fg_0_19) AS sumfg019, SUM(fg_20_29) as sumfg2029, SUM(fg_30_39) as sumfg3039, SUM(fg_40_49) AS sumfg4049, SUM(fg_50p) as sumfg50p, SUM(xpa) as sumxpa, SUM(fga) as sumfga, SUM(xp) as sumxp, playerstats.player_id, year, players.nflteam_id, nflteam_abbv, COUNT(week) as gpm, players.player_id, positions.position_abbv, FROM playerstats, players, nflteams
WHERE playerstats.player_id = players.player_id
  AND nflteams.nflteam_id = players.nflteam_id
  GROUP by playerstats.player_id, year
if (!$result = mysql_query($sql))
    die("Could not get the statistics you requested: " . mysql_error() . ".<br />");

$stat_list = array();
while ($row = mysql_fetch_array($result))
  $row['fgm'] = (($row['sumfg019']) + ($row['sumfg2029']) + ($row['sumfg3039']) + ($row['sumfg4049']) +
  $row['fgmiss'] = ( $row['fgm'] == 0 ) ? 0 : (($row['sumfga']) - ($row['fgm']));
  $row['xpm'] = ( $row['sumxp'] == 0 ) ? 0 : (($row['sumxpa']) - ($row['sumxp']));
  $row['ffr_points'] = (($row['sumrushyd'] * 0.067) + ($row['sumrushtd'] * 6) + ($row['sumrecyd'] * 0.067)    + ($row['sumrecatt'] * 1) + ($row['sumrectd'] * 6) + ($row['sumtwopt'] * 2) + ($row['sumfmbl'] * -2)    + ($row['sumpassyd'] * 0.034) + ($row['sumpasstd'] * 6) + ($row['sumqbint'] * -2) + ($row['sumfg019'] * 3) + ($row['sumfg2029'] * 3) + ($row['sumfg3039'] * 3) + ($row['sumfg4049'] * 4) + ($row['sumfg50p'] * 5) + ($row['sumxp'] * 1) + ($row['xpm'] * -1) + ($row['fgmiss'] * -1));
    $stat_list[] = $row;

I know its a big ass query, but I'm still learning.

...Just a really quick demo here of the random record:
[a href=\"http://www.fantasyfootballresearch.com/football/test123.php\" target=\"_blank\"]http://www.fantasyfootballresearch.com/football/test123.php[/a]

Share this post

Link to post
Share on other sites
Slow down there, cowboy -- did wickning1's formal solution work for you? That's a 3-table multi-select, and with all that code, there's bound to be something that's difficult to see. Use the more explicit JOIN syntax, and forget about the complex column list to return; just make sure you get back the right uids from the initial table.

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.