Retrieve values from 2 tables with minimal queries

[Marco van Herwaarden]

Originally Posted by sully02

1 - I want to select all values from the draftpicks table
4 - I will use the teamid field to look up the teamname field and put that in on my template
5 - I will use the playerid field to look up the playername and school fields to put those on my template

So you don't want to select only the columns from the draftpicks table, but also want the playername and teamname.

The WHERE clause you only add once on the end.

I added a $selectedpick field as an example of how to select all info for just 1 draft. This will first (with the WHERE clause) select all rows from your primary table (draftpicks) with pickid = $selectedpick, then it will find all corresponding rows from both the teams and the players tables, based on the selection following the "ON" in their respective LEFT JOIN clauses.

You query would look like this:
[sql]SELECT draftpicks.*, teams.teamname, players.playername
FROM lmd_draftpicks AS draftpicks
LEFT JOIN lmd_teams AS teams ON (teams.teamid = draftpicks.teamid)
LEFT JOIN lmd_players AS players on (players.playerid = draftpicks.playerid)
WHERE draftpicks.pickid = $selectedpick[/sql]