| Joining 3 Msql Tables
How to link 3 mysql tables together |
Steven_G
msg:4269897 | 8:35 pm on Feb 20, 2011 (gmt 0) |
Folks,
I am relatively new to php but i am learning slowly. I am building a sports database website / cms, which is getting there very nicely.
I am having an issue joining 3 tables together, if i use then as seperate queries they do 'exactly' what i want then to do, however i am at a loss as to how to join them together.
As far as i am aware i do not need to change anything in the database itself i just need to get the query correct.
The following select queries work perfectly seperately: -
Query 1
$get_players = mysql_query("
SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPublish,
P.PlayerNumber AS number
FROM idihc_seasons S
LEFT OUTER JOIN idihc_players P ON P.PlayerID = S.SeasonPlayerID
WHERE seasonid = 5 and P.PlayerPublish = 1
GROUP BY id
ORDER BY number
",$connection)
or die(mysql_error());
Query 2
$get_players = mysql_query("
SELECT *
FROM idihc_seasonnames
WHERE SeasonPublish = 1
",$connection)
or die(mysql_error());
In query 1 i would like 'WHERE seasonid = 5' to be 'WHERE SeasonPublish = 1' from query 2.
These are the follow tables from the database: -
`idihc_players` (
`PlayerID` int(10) unsigned NOT NULL auto_increment,
`PlayerFirstName` varchar(32) NOT NULL default '',
`PlayerLastName` varchar(64) NOT NULL default '',
`PlayerNumber` tinyint(3) unsigned NOT NULL default '0',
`PlayerPublish` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`PlayerID`)
`idihc_seasons` (
`SeasonID` int(10) unsigned NOT NULL default '0',
`SeasonPlayerID` int(10) unsigned NOT NULL default '0',
KEY `SeasonID` (`SeasonID`),
KEY `SeasonPlayerID` (`SeasonPlayerID`)
`idihc_seasonnames` (
`SeasonID` int(10) unsigned NOT NULL auto_increment,
`SeasonName` varchar(64) NOT NULL default '',
`SeasonPublish` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`SeasonID`)
The database works exactly how i want it to in the CMS so i would rather not be changing it at this stage.
I am trying to only display the players that are in a season that is published.
I hope all this makes sense, and look forward to any help you can offer.
Steve
|
rocknbil
msg:4270156 | 5:40 pm on Feb 21, 2011 (gmt 0) |
As you have already discovered, a join requires a unique field on one of the other tables to correctly relate to that table. The only one I see ATM is SeasonID. Try this.
SELECT idihc_players.PlayerID AS id,
idihc_players.PlayerLastName AS lastname,
idihc_players.PlayerFirstName AS firstname,
idihc_players.PlayerPublish as ppublish,
idihc_players.PlayerNumber AS number,
idihc_seasonnames.SeasonID as sid,
idihc_seasonnames.SeasonName as sname,
idihc_seasonnames.SeasonPublish as spublish
FROM idihc_seasons
LEFT OUTER JOIN idihc_players on idihc_players.PlayerID = idihc_seasons.SeasonPlayerID
left join idihc_seasonnames on idihc_seasonnames.SeasonID = idihc_seasons.SeasonID
WHERE idihc_seasons.seasonid = 5 and idihc_players.PlayerPublish = 1
GROUP BY id
ORDER BY number
I used the table names instead of the placeholders for clarification, mod as you wish.
|
Steven_G
msg:4270172 | 6:16 pm on Feb 21, 2011 (gmt 0) |
Rocknbil,
Thank you for your response, just prior to seeing you response i got the script to work acciendetly by playing around.
Here is what i have done that works.
$get_players = mysql_query("
SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPositionID AS position,
P.PlayerNumber AS number,
P.PlayerPublish,
P.PlayerNationalityID AS nationality,
SN.SeasonID,
SN.SeasonPublish
FROM idihc_seasons S
LEFT OUTER JOIN idihc_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN idihc_seasonnames SN ON SN.SeasonID = S.SeasonID
WHERE SN.SeasonPublish = 1 and P.PlayerPublish = 1
GROUP BY id
ORDER BY number
",$connection)
or die(mysql_error());
My next challenge is to join another table to the above, however if i am understaning you correctly i need to have a unique field between the two tables i am trying to join.
With this one though i would like to display the text data in the second table rather than the id that is linked between them. I can currently get the query to display the 'id' which is the unique filed but it wont change it to the new value.
i.e.
I have the 'idihc_players' table which has in it 'PlayerPositionID'
I also have 'idihc_playersposition' which has 'PlayerPositionID' and 'PlayerPositionName'
what i wish to display is the 'PlayerPositionName' from the 'id' it finds, however i just get a blank and no data populated.
Thanks
Steve
|
Steven_G
msg:4270213 | 7:39 pm on Feb 21, 2011 (gmt 0) |
Folks,
I have tried different ways to add this table but to no alas.
This is what i have :-
$get_players = mysql_query("
SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPositionID,
P.PlayerNumber AS number,
P.PlayerPublish,
P.PlayerNationalityID AS nationality,
idihc_playerpositions.PlayerPositionID,
idihc_playerpositions.PlayerPositionName AS position,
SN.SeasonID,
SN.SeasonPublish
FROM idihc_seasons S, idihc_playerpositions
LEFT OUTER JOIN idihc_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN idihc_seasonnames SN ON SN.SeasonID = S.SeasonID
LEFT OUTER JOIN idihc_playerpositions ON P.PlayerPositionID = idihc_playerpositions.PlayerPositionID
WHERE SN.SeasonPublish = 1 AND idihc_playerpositions.PlayerPositionID = idihc_playerpositions.PlayerPositionName and P.PlayerPublish = 1
GROUP BY id
ORDER BY number
",$connection)
or die(mysql_error());
I get this error: -
Not unique table/alias: 'idihc_playerpositions'
I have read and re-read the mysql tutorials on Joins however i must be missing something. As per my second post the two joins work very well its just when i add the 'playerposition' join that is causing me the problems.
Thanks in advance
Steve
|
LifeinAsia
msg:4270219 | 7:47 pm on Feb 21, 2011 (gmt 0) |
Any particular reason you're not using a table alias for idihc_playerpositions?
|
Steven_G
msg:4270224 | 8:01 pm on Feb 21, 2011 (gmt 0) |
LifeinAsia,
I tried using the alias PP but again doesn't matter which way round i do it, it comes back with the same error.
Steve
|
LifeinAsia
msg:4270234 | 8:16 pm on Feb 21, 2011 (gmt 0) |
Oops- I missed this the first time through:
FROM idihc_seasons S, idihc_playerpositions
LEFT OUTER JOIN idihc_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN idihc_seasonnames SN ON SN.SeasonID = S.SeasonID
LEFT OUTER JOIN idihc_playerpositions ON P.PlayerPositionID = idihc_playerpositions.PlayerPositionID |
|
You are joining the same table twice. You can do that, but you'll need to use different table aliases for each one.
|
Steven_G
msg:4270251 | 8:35 pm on Feb 21, 2011 (gmt 0) |
I tried a new alias 'B' and changed the 'FROM idihc_seasons S, idihc_playerpositions' to 'FROM idihc_seasons S, idihc_player B'
But again get the same error: -
Not unique table/alias: 'B'
Think i have been starring at this too long now as i can't figure it it out.
|
LifeinAsia
msg:4270286 | 9:28 pm on Feb 21, 2011 (gmt 0) |
The question I have is do you really need to join the table twice?
|
|
|
