Tough query...possible?

esiason14

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

	FROM players p, nflteams t, playerstats ps, positions pos
	WHERE p.lname LIKE '%$entered_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.lname 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 years are in the playerstats table. I also have an auto incrementing stat_id associated to every stat record. I've tried several other variations, but just can't seem to get it right. Can anyone help me get on the right track here.

Is it possible to find the high stat_id for each player...and then use that nflteam_id from that record? is there a better way?

kakki

i am a bit confused, why did you run a group by on your results???

maybe you can do a subquery where you find the highest value of years
like

SELECT [I]whatever[/I]
FROM players p, nflteams t, playerstats ps, positions pos 
WHERE p.lname LIKE '%$entered_term%'                        

    AND ps.nflteam_id = t.nflteam_id
    AND ps.player_id = p.player_id
    AND p.position_id = pos.position_id 
    AND ps.stat_id IN 
                   ( SELECT MAX(stat_id) FROM playerstats GROUP BY player_id )

esiason14

kakki, I'll give that query a shot. I used the group by player_id, otherwise it listed all yearly records for that player.....I'm only looking for one...the most recent

esiason14

I tried the proposed query, but it seems to run and run....no results are returned.

I'm not sure if it makes a difference, but I'm using MySQL 4.1.9