CODEWITHSUNDEEP
phpmysql
Ahmad
Ahmad

Reputation: 72

Join 2 tables to get 2 related records Mysql PHP

I have two tables like so:

# Match 
id, team1_id , team2_id
-----------------------
1, 10, 20
2, 10, 50



# Team 
id, team_name, team_country
---------------------------
10, team A , England
20, team B , France

I'm trying to get the list from Match table with both teams info, I wanna some thing like :

Team A (England)  vs Team B (France)

I tried this one, but I got false team info, some thing wrong with my query for sure.

Here's my query :

  SELECT `match`.*,
  `t1`.`team_country` as team1_country,
  `t2`.`team_country` as team2_country
  FROM `match`,
  `team` t1 , `team` t2
  WHERE `match`.`team1_id` = `t1`.`id` and `match`.`team2_id` = `t2`.`id`

Thanks in advance!

Upvotes: 1

Views: 34

Answers (1)

bloodstix
bloodstix

Reputation: 142

I just fiddled it on my testmachine with postgres. The SQL shouldn't be different:

lara=# create table match ( id serial primary key, team1 int, team2 int);
CREATE TABLE
lara=# create table teams ( id serial primary key, name text, country text);
CREATE TABLE
lara=# insert into match(id, team1, team2) values (1,1,2),(2,1,3),(3,2,1);
INSERT 0 3
lara=# select * from match;
 id | team1 | team2
----+-------+-------
  1 |     1 |     2
  2 |     1 |     3
  3 |     2 |     1
(3 rows)

lara=# insert into teams values (1, 't1', 'en');
INSERT 0 1
lara=# insert into teams values (2, 't2', 'de');
INSERT 0 1
lara=# insert into teams values (3, 't3', 'fr');
INSERT 0 1
lara=# select * from match m left join teams t1 on t1.id=m.team1 left join teams t2 on t2.id=m.team2;
 id | team1 | team2 | id | name | country | id | name | country
----+-------+-------+----+------+---------+----+------+---------
  1 |     1 |     2 |  1 | t1   | en      |  2 | t2   | de
  2 |     1 |     3 |  1 | t1   | en      |  3 | t3   | fr
  3 |     2 |     1 |  2 | t2   | de      |  1 | t1   | en

So your actual query is correct. A cleaner one would be the following:

SELECT * FROM match m 
LEFT JOIN teams t1 ON t1.id=m.team1 
LEFT JOIN teams t2 ON t2.id=m.team2;

But your problem is obviously not the SQL.

Upvotes: 1

Related Questions