CODEWITHSUNDEEP
mysqlsqldatabase
steven_corner
steven_corner

Reputation: 15

Joining two SELECT statements into one table

I am trying to combine the output of two separate SQL statements into one table. I have a list of team names with associated id's in one database and another database with a list of matches in another. e.g

Teams    
-----------------                            
id   name              
1    Arsenal                    
2    Aston Villa

Matches
--------------------------------
id   home_team_id   away_team_id
1    1                2

In the matches database, home_team_id and away_team_id are foreign keys of the id in the teams database. What I am trying to do is output the match id with the team names associated with the id's in the matches database. Expected output:

match_id        home_team_name      away_team_name
---------------------------------------------------
1               Arsenal             Aston Villa

I have two select statements at the moment.

SELECT match_id, name as home_team_name
FROM matches, teams
WHERE home_team_id = id;

SELECT match_id, name as away_team_name
FROM matches, teams
WHERE away_team_id = id;

The first select statement outputs the match id and the name of the home team, the second outputs the match id and the name of the away team.

Is there any way of achieving the desired output with my current database design?

Upvotes: 1

Views: 372

Answers (2)

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7302

You can have a table in a from clause multiple times if you define aliases for them (you can only leave one without an alias, so the table name is used as alias).

So just do it as:

select *
from matches
join teams t1 on t1.id = matches.home_team_id
join teams t2 on t2.id = matches.away_team_id

or you can do it as

select *
from matches, teams t1, teams t2
where t1.id = matches.home_team_id and t2.id = matches.away_team_id

and it is much better to user ANSI JOINs (first syntax), it is more readable, and can handle ugly things when number of tables grows with some left/right joins.

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

You can do:

SELECT m.id, t1.name as home_team_name, t2.name as away_team_name
FROM `match` m
INNER JOIN teams t1 ON t1.id = m.home_team_id   
INNER JOIN teams t2 ON t2.id = m.away_team_id

This JOINS the match table with teams table twice, in order to get both team's names.

sqlfiddle demo

Note that i'm escaping `match` as it is a reserved word

Upvotes: 3

Related Questions