Query help

[esiason14]

Here is my current query that searches for a user entered player name.

$sql = "SELECT p.*, t.*
	FROM players p, nflteams t, playerstats ps, positions pos
	WHERE p.$search_sql_cat LIKE '%$search_term%'						
	AND ps.nflteam_id = t.nflteam_id
	AND ps.player_id = p.player_id
	AND p.position_id = pos.position_id
	GROUP by p.player_id
	ORDER BY p.$search_sql_cat ASC";

I almost get the results I want except for the team abbreviation.
If I search for a player with the last name of Moss, it returns:

Santana Moss NYJ
Randy Moss MIN

The teams that are returned are not the teams they currently play for. I want to find the team that they played for most recently. The teams shown above are the teams they first played for in their career. The year is in the playerstats table. Can anyone help me get on the right track here.

[tomhath]

Probably something like this. You'll have to supply the column name that holds the year:

    SELECT p.*, t.*
    FROM players p
    INNER JOIN playerstats ps ON ps.player_id = p.player_id
    INNER JOIN nflteams t        ON ps.nflteam_id = t.nflteam_id
    INNER JOIN  positions pos  ON  p.position_id = pos.position_id
    WHERE p.$search_sql_cat LIKE '%$search_term%'                        

      AND NOT EXISTS (
            SELECT 1 FROM  playerstats ps2 
            WHERE ps2.player_id = ps.player_id AND ps2.year > ps.year
       )
    ORDER BY p.$search_sql_cat ASC

[esiason14]

Thanks for the response. I don't believe that will work.

Is it possible to do something like...where ps.year = MAX(ps.year)

[Piranha]

I can't see for sure how the link between players and mflteams are handled, but I think it's a many-to-many relationship with playerstats. Then something like this should probably work:

$sql = "SELECT p.*, t.*
    FROM players p, nflteams t, playerstats ps, positions pos
    WHERE p.$search_sql_cat LIKE '%$search_term%'                        

    AND ps.nflteam_id = t.nflteam_id
    AND ps.player_id = p.player_id
    AND p.position_id = pos.position_id
    GROUP by p.player_id, 
    ORDER BY p.$search_sql_cat ASC", ps.year desc;

But I suggest that you add a column to the playerstats table instead, with the name "active". When a person is active in a team set active to 1, when a person leaves a team set it to 0. Then it's easy to find the current team.

[Lars_Berg]

Your group by clause is incorrect.

SELECT p.*, t.*
    FROM players p join playerstats ps
      on p.player_id = ps.player_id
      join positions pos
      on p.position_id = pos.position_id
      join nflteams t
      on ps.nflteam_id = t.nflteam_id
    WHERE p.$search_sql_cat LIKE '%$search_term%'                        

     and ps.year =
      (select max(pss.year)
         from playerstats pss
        where pss.player_id = ps.player_id)
    ORDER BY p.$search_sql_cat ASC