Joining 3 MySQL Tables

[Steve Grout]

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 separate queries they do 'exactly' what i want them 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.

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());

I am now trying to join data which is held in a second column on a new table, which is linked via the id 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.

What i am trying to add: -

I have the 'idihc_players' table which has in it 'PlayerPositionID' (Which is already being used in the above query)
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

[r937]

what is the reason for using GROUP BY?

what if a player plays for more than one season? you won't get anything meaningful with that GROUP BY

[Steve Grout]

r937,

Thanks for your reply.

The reason I am using GROUP BY is to stop all the seasons being published. The CMS i have created caters for players in different seasons.

What happens with the query that works is: -

If a season is published it displays all players in that season as long as that player is also published.

If a season is published and a player is not published, that player will not show in the list.

Hope that all makes sense. It works very well in the CMS I just need to try and join the table to what I am doing.

Steve

[r937]

The reason I am using GROUP BY is to stop all the seasons being published. The CMS i have created caters for players in different seasons.

but if you only GROUP BY the player, you won't know which season is being displayed for that player

what's worse, different players could have different seasons published

[Steve Grout]

Sorry I am not making myself very clear.

On the site only ONE season will ever be displayed, this is all controlled by the CMS, therefore a admin user will know what players are being displayed in which season as I have made that available there.

Everything that gets passed through to the website for all to view will only ever be one season and everything that connects to that season.

Hopefully I have explained myself a little better.

Question I have, does the GROUP BY really have an impact on the JOIN that I am trying to achieve? I didn't think this would have an impact.

[r937]

On the site only ONE season will ever be displayed...

if this is true, please remove the GROUP BY and confirm that it produces the same results

[Steve Grout]

Ok I will try this when I am back home again, what results are you expecting this to produce?

[Steve Grout]

R937

I have just removed GROUP BY and this had no effect on the results that I am achieving. Are you saying this is what was causing the problem when trying to join the other table?

This is the result URL
http://grout-systems.com/PHP-Login/roster.php

As you can see under the heading 'position' I am only getting the actual 'id', hence why I wish to join the existing table to 'idihc_playerpositions' to enable me to get the actual name from idihc_PlayerPositionName' for it to be displayed.

I will point out I do change the 'P.PlayerPositionID AS position,' field from the original query to 'P.PlayerPositionID', and amend accordingly in the third join.

[r937]

Are you saying this is what was causing the problem when trying to join the other table?

no, i am not -- i hadn't got as far as your problem of joining any other tables, i was trying to understand your original query

by the way, regarding this part of your query --

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

the conditions in the WHERE clause negate the outer joins, so you might as well code them as INNER JOINs, they might run faster

okay, so your original query will return all publishable players in a single season

could you please now describe the additional tables, and indicate the one-to-many relationships they have with the tables in your original query

[Steve Grout]

r937

Many thanks for your reply, I am not sure what I was thinking about when I done OUTER JOINS, as you rightly point out, the OUTER JOINS are negated by the WHERE clause. DUH!!

I have since re-written the script using inner joins and this works perfectly.

Many thanks again for your help.

Steve

[r937]

what about adding the other tables?

[Steve Grout]

I was trying to add the other tables as outer joins and it didn't like it. I have put them all as inner joins and all three tables worked perfectly.