Joining 3 Msql Tables - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Joining 3 Msql Tables

How to link 3 mysql tables together

Steven_G

8:35 pm on Feb 20, 2011 (gmt 0)

10+ Year Member

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

5:40 pm on Feb 21, 2011 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

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

6:16 pm on Feb 21, 2011 (gmt 0)

10+ Year Member

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

7:39 pm on Feb 21, 2011 (gmt 0)

10+ Year Member

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

7:47 pm on Feb 21, 2011 (gmt 0)

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

Any particular reason you're not using a table alias for idihc_playerpositions?

Steven_G

8:01 pm on Feb 21, 2011 (gmt 0)

10+ Year Member

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

8:16 pm on Feb 21, 2011 (gmt 0)

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

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

8:35 pm on Feb 21, 2011 (gmt 0)

10+ Year Member

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

9:28 pm on Feb 21, 2011 (gmt 0)

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

The question I have is do you really need to join the table twice?