Fetching data from multiple tables in one MySQL query

Often we need to use SELECT queries that will fetch data from multiple tables. One option is to use INNER JOIN, LEFT JOIN etc. But in this case described below I couldn’t find a way to use those. Lets say 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.

Here’s a simple way to do it -

SELECT tableA.id,
(SELECT tableB.team_name FROM tableB WHERE tableA.home_id = tableB.id) AS home_team,
(SELECT tableB.team_name FROM tableB WHERE tableA.away_id = tableB.id) AS away_team,
tableA.datetime
FROM tableA
WHERE tableA.status < 4;