Fetching data from multiple tables in one MySQL query – Part 2

A few days back, I mentioned in a post how to fetch data from multiple tables in one MySQL query using multiple and nested SELECT statements. Today I’m going to present a more elegant way to do that using INNER JOIN.

Let’s reiterate the problem first -

We have 2 tables – tableA and tableB. The structure of these 2 tables are like -

tableA

`id` int(11) NOT NULL AUTO_INCREMENT,
`home_id` int(11) DEFAULT NULL,
`away_id` int(11) DEFAULT NULL,
`datetime` datetime DEFAULT NULL,
`status` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)

tableB

`id` int(11) NOT NULL AUTO_INCREMENT,
`team_name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)

Here home_id and away_id in tableA are foreign keys that references to id in tableB. We want to pick data from tableA according to some condition – for example, status is less than 4. But we want team names instead of ids. That means we need to pick team names from tableB.

Now if we needed just home team or away team, that would have been easy as we can use an INNER JOIN straightway -

SELECT tableA.id,
tableB.team_name AS home_team,
tableA.datetime
FROM tableA
INNER JOIN tableB
ON tableA.home_id = tableB.id
WHERE tableA.status < 4;

But we need both home and away team and they refer to 2 different rows on tableB. So, it is not possible to pick both the names using one INNER JOIN. Difficult situation, huh? Aliases come to the rescue! We can use an alias for tableB and inner join that to the first 2 tables. Here’s the full query -

SELECT tableA.id,
tableB.team_name AS home_team,
tableB_Alias.team_name AS away_team,
tableA.datetime
FROM tableA
INNER JOIN tableB
ON tableA.home_id = tableB.id
INNER JOIN tableB AS tableB_Alias
ON tableA.away_id = tableB_Alias.id
WHERE tableA.status < 4;