Quick query help

[esiason14]

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

[khendar]

[code]
select players.player_id, positions.position_abbv
from players, positions
where players.position_id = positions.position_id
[/code]

I think...

[wickning1]

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]

[esiason14]

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]

[fenway]

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.