0

tbl_teams: team_id | team_name
tbl_players: player_id | player_fname | player_sname | player_bplace | player_bdate
tbl_players_stats: player_id | season_id | player_squad_no | team_id | player_apps | player_goals

Sorry if this is a basic question, but from all the MySQL tables and columns above I'd like to join the tables and then display the results by which season_id and team_id is selected. I need using PHP like this:

player_squad_no | player_sname, player_fname | team_name | player_apps | player_goals

I've looked at examples on here but still can't figure out how to write the MySQL query to do it with three separate tables and how to specify the table name before the column name. I've seen some examples with only the initial. tt.teams for instance. Is Left Join the way to do it?

Any help would be much appreciated.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
julescoco
  • 17
  • 4
  • 4
    There are plenty of questions like this.. http://stackoverflow.com/questions/16222097/mysql-left-join-3-tables – Sterling Archer Sep 04 '13 at 17:27
  • 1
    or even http://stackoverflow.com/questions/9722801/sql-left-joining-multiple-tables – Sterling Archer Sep 04 '13 at 17:28
  • Why are `player_stats` and `players` in separate tables? – Explosion Pills Sep 04 '13 at 17:28
  • @ExplosionPills I've used a separate table for each because there will be different player_stats for each season. – julescoco Sep 04 '13 at 17:32
  • "Is Left Join the way to do it?" Some kind of `JOIN` is almost definitely the way to do it. Which one depends on what data you want to return. [Here's a great reference for understanding the different types of JOIN.](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) (Note that the syntax varies between SQL implementations, and MySQL doesn't support full outer joins) – Air Sep 04 '13 at 17:45
  • Thanks for all the links and examples. The codinghorror link looks like a great read and easy to understand. Thanks again. :) – julescoco Sep 04 '13 at 17:57

4 Answers4

1

With three separate tables, you simply write the join like this:

SELECT *
FROM Table_A AS A
LEFT JOIN Table_B AS B USING(ID)
LEFT JOIN Table_C AS C USING(ID)

Note that USING(column) is a syntactic alternative to ON A.column = B.column that you can use when the columns you want to join on have the same name in both tables.

In the above example, the tables are aliased with AS so that you can refer to them by the alias instead of the full table name. (AS is actually optional; you can just give the alias immediately after the table, if you're paying by the character.) Try to choose an alias that makes sense when you look at it; often times people will alias like this:

SELECT a.Name, b.State
FROM Customers AS a
LEFT JOIN Orders AS b
...etc.

But if you have a longer query, how are you supposed to remember what tables a and b refer to? At the very least, it would make sense to alias Customers AS C and Orders AS O; in some cases, I would go a step further: Registration AS REG, for instance. This gets more and more important as you JOIN more and more tables together.

Here's one way to write your query:

SELECT
    Stats.player_squad_no,
    CONCAT_WS(', ', Players.player_sname, Players.player_fname) AS player_full_name,
    Teams.team_name,
    Stats.player_apps,
    Stats.player_goals
FROM tbl_players AS Players
LEFT JOIN tbl_players_stats AS Stats USING(player_id)
LEFT JOIN tbl_teams AS Teams USING(team_id)

The CONCAT_WS() function is included to assemble the player's full name the way you indicated you wanted it to be displayed. Since this function will output a column with a messy name, I also gave it an alias.

Air
  • 8,274
  • 2
  • 53
  • 88
0
    SELECT player_squad_no , player_sname, player_fname,team_name, player_apps, player_goals
 FROM tbl_players_stats as s 
JOIN tbl_players as p ON s.player_id=p.player_id 
JOIN tbl_teams as t ON s.team_id=t.team_id
Mihai
  • 26,325
  • 7
  • 66
  • 81
0

This should work

SELECT tbl_players_stats.player_squad_no, 
    tbl_players.player_sname, 
    tbl_players.player_fname, 
    tbl_teams.team_name, 
    tbl_players_stats.player_apps, 
    tbl_players_stats.player_goals
FROM tbl_players 
    JOIN tbl_players_stats ON tbl_players.player_id = tbl_players_stats.player_id 
    JOIN tbl_teams ON tbl_teams.team_id = tbl_players_stats.team_id
-1

Nothing Joining is simple concept. But we should use proper columns for tables. While selecting the list of columns to select we should be little careful by using table aliasing. Try the below code

select c.player_squad_no,b.player_sname,b.player_fname,a.team_name,c.player_apps,c.player_goals
  from tbl_teams a,tbl_players b,tbl_players_stats c
 where a.team_id=c.team_id 
   and b.player_id=c.player_id
Sarma Mullapudi
  • 110
  • 1
  • 6
  • 1
    Not to sound critical, because your answer is good, but even better would be to make the table aliases self documenting. For example, rather than just a,b,c go with something like ... FROM tbl_teams Teams, tbl_players Players, tbl_players_stats Stats – Strixy Sep 04 '13 at 17:58
  • 1
    Please also consider making a habit of using the explicit `JOIN` syntax whenever possible. See also the answers to this SO question: [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/q/1018822/2359271) – Air Sep 04 '13 at 18:07