February 7, 2010 in Blog, Coding & Scripting by Sanny

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;

February 2, 2010 in Blog, Coding & Scripting by Sanny

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;

It is important to use aliases here. You have to specify ‘AS home_team’ or ‘AS away_team’ etc in the inner SELECT commands.

There may be more efficient and elegant methods than this – using multiple selects in one query. Can anybody suggest some?