Page 1 of 1

query help

Posted: Tue Sep 05, 2006 9:22 pm
by esiason14
Here is my current query that searches for a user entered player name.

Code: Select all

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've tried several other variations, but just can't seem to get it right. Can anyone help me get on the right track here.

Posted: Wed Sep 06, 2006 7:22 am
by GM
Can you show the SELECT field1, field2 ... part of your query as well please?

Also, I think we'd need to see the structure of the tables (at least the primary key and foreign key fields, anyway).