Jump to content

Quick query help


esiason14

Recommended Posts

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

Positions
Columns (postion_id, position_abbv)

Players
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
Link to comment
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]
Link to comment
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
[code]
$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
  ORDER by RAND() LIMIT 1";
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['sumfg50p']));
  $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;
}[/code]


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]
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.