Well, Cnghl, first I am not a database query expert, but will take a stab at it…
( You are actually talking about two queries not two tables. )
But, first, normally how you create a join is to list the items in each table and then list what you want out
of them. Then, write the join as needed to get the correct data pulled out. As I see it so far, this is your list:
Table 1: (Actually Query 1 !)
DraftTeam
DraftID
Players
CNDraftPOS
FullName
CNDraftYR
PlayerID
WHERE DraftTeam.DraftID=$iDraftID AND Players.CNDraftYR=$iCNDraftYR
ORDER BY Players.CNDraftPOS;
Table 2:
DraftTeam
DraftID
Goalies
CNDraftPOS
FullName
CNDraftYR
GoalieID
WHERE DraftTeam.DraftID=$iDraftID AND Goalies.CNDraftYR=$iCNDraftYR
ORDER BY Goalies.CNDraftPOS
So, to do a three table join, you actually do it in two steps. The first is one join that creates a new and
temporary dataset which you join with the third table to create the finished dataset. The three table code
is loosely like this: ( taken from the second tutorial below… )
SELECT t1.col, t3.col FROM table1
join table2 ON table1.primarykey = table2.foreignkey
join table3 ON table2.primarykey = table3.foreignkey
As you see the structure is quite simple. The only catch to this is that you need to know your table’s full
structure and how each links to the others to end up with the correct data. In some cases, your second
table might be table2 or table3 depending on how you want the data joined together. This depends on the
fields that you need to join on. Your data is a bit confusing to me. First, your tables separate players and goalies. Since goalies are players, you should just have one table for both with an indicator field that notes if the player is a goalie or not. Then, you queries would be less complicated. But, in your case, I think it is something like the following. (not tested of course as I do not have your data to use…)
[php]
SELECT
DraftTeam.DraftID,
Players.CNDraftPOS, Players.FullName, Players.CNDraftYR, Players.PlayerID,
Goalies.CNDraftPOS, Goalies.FullName, Goalies.CNDraftYR, Goalies.GoalieID
FROM DraftTeam
LEFT JOIN Players ON DraftTeam.DraftID = $iDraftID AND Players.CNDraftYR
LEFT JOIN Goalies ON DraftTeam.DraftID = Goalies.DraftID
WHERE DraftTeam.DraftID = $iDraftID AND Players.CNDraftYR = $iCNDraftYR AND Goalies.CNDraftYR = $iCNDraftYR
[/php]
One note on this query is that you may have to alter the names of the cols because you have duplicates in
your tables. Therefore, since you are merging two tables into one table, “FULLNAME” col’s would be listed
twice since you have them in both tables. (That is why I suggested merging goalies with players and just
adding a col that indicates they are a goalie… ) You would have to add an “AS zzz” to the select to solve it.
Basically, this combines the two queries you posted removing the duplicates and combining the WHERE
clauses. It should work, but, not sure if that is what you really want for a result. You should test it and
have your page display the results of the query to see if it is exactly what you need.
Here is a simple tutorial on how to join tables. Click on the “Next Chapter” button to move thr the tutorial
pages. It is a simple explanation of joins…
http://www.w3schools.com/sql/sql_join.asp
And, for combining three or more tables in one query, here is another one:
http://javarevisited.blogspot.com/2012/11/how-to-join-three-tables-in-sql-query-mysql-sqlserver.html
They might help you understand joins… Good luck…
