query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
esiason14
Forum Newbie
Posts: 10
Joined: Sun Jul 17, 2005 12:19 pm

query help

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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).
Post Reply