Selecting rows from two tables MySql

Question

I have two tables - player, which stores players data, and similar table team. I want to select from these two tables name and surname of player and name of team to which one he belongs. I used this query:

SELECT player.name, player.surname,
   team.name AS teamname 
FROM player, team 
WHERE player.id_player=team.id_team

But as result i only have only one player from each team, and of course i want a full list of players with names of team.

Anyone has an idea?

Player has id_player, name, surname, and team_id_team. Team has id_team and name — Janusz Bieraszewski
– Janusz Bieraszewski, Commented Jan 9, 2016 at 18:24

Gordon Linoff · Accepted Answer · 2016-01-09 18:23:48Z

3

I think you need to join on the team id. I am guessing something similar to this will work:

SELECT p.name, p.surname, t.name AS teamname 
FROM player p JOIN
     team t
     ON p.team_id_team = t.id_team ;
----------^ Note the change here

Also:

Learn proper JOIN syntax. Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.
Table aliases make a query easier to write and to read.
If you have players without a valid team, then use a LEFT JOIN instead of JOIN.

answered Jan 9, 2016 at 18:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick Lee Over a year ago

Good catch on the error in the join condition. Based on the schema provided in the comment, it certainly seems you have the correct answer.

Janusz Bieraszewski Over a year ago

With this query i have a valid teams, left join dont work, whats the difference?

Bernd Buffen · Accepted Answer · 2016-01-09 18:23:34Z

2

You must use JOIN to "connect" the tables. With "WHERE" you only can exclude rows;

SELECT
  p.name,
  p.surname,
  t.name AS teamname 
FROM player p
LEFT JOIN team t ON p.id_player = t.id_team ;

answered Jan 9, 2016 at 18:23

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

5 Comments

Janusz Bieraszewski Over a year ago

this query shows wrong names of teams, and when it runs out of teamnames shows null, Grodon Linoff's answer works well

Evgeny Over a year ago

@Janusz Bieraszewski this is because there is typo in your query and author of this answer just copy it

Bernd Buffen Over a year ago

@Evgeny - Yes i have seen the difference in the field names, but i canz see othe relations

Gordon Linoff Over a year ago

This is because the join condition is incorrect. I understand the answer, but not the upvotes.

Bernd Buffen Over a year ago

@Janusz Bieraszewski - if you post your Table schema i can correct my answer

Evgeny · Accepted Answer · 2016-01-09 18:23:49Z

1

You should use left join:

SELECT p.name, p.surname,
   t.name AS teamname 
FROM player p
left join team t on t.id_team=p.id_team

answered Jan 9, 2016 at 18:23

Evgeny

4,0192 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

Selecting rows from two tables MySql

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related