SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining 3 MySQL Tables

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Steve Grout View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Steve Grout View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I will try this when I am back home again, what results are you expecting this to produce?

  8. #8
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Steve Grout View Post
    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 --
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    what about adding the other tables?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Apr 2009
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •