Apologies for the cryptic title, I don't quite know how to title this question. I'm asking here rather than StackOverflow since I've got a working query, I'm just wondering if it can be improved.
Anyway, I've got three tables, administration
, mapping
and used_mappings
. Administration
holds details of individual financial administrations, mapping
is a "static" table holding details for all available financial mappings, used_mappings
holds records of what mappings are used by what administration (not every administration uses every mapping). The used_mappings
table also has a column, custom_name
, so the columns look as follows;
Administration
| id | name |
|----|---------|
| 1 | Admin 1 |
| 2 | Admin 2 |
Mapping
| id | name |
|----|-----------|
| 1 | Mapping 1 |
| 2 | Mapping 2 |
| 3 | Mapping 3 |
| 4 | Mapping 4 |
| 5 | Mapping 5 |
Used mapping
| id | administration_id | mapping_id | custom_name |
|----|-------------------|------------|-------------------|
| 1 | 1 | 1 | Revenue |
| 2 | 1 | 3 | Cost of sales |
| 3 | 2 | 1 | Bar revenue |
| 4 | 2 | 3 | Cost of bar sales |
| 5 | 2 | 4 | Wages |
I now need to fetch all records from mapping
, regardless of them being used, and where a custom_name
is defined in used_mappings
for a specific administration, add it on as an extra column (and have custom_name
in the results be null
if no custom_name
exists.)
I first tried a simple LEFT JOIN
;
SELECT M.id,
M.name,
U.custom_name as customName
FROM mapping M
LEFT JOIN used_mapping U ON U. = M.id
WHERE U.administration_id = 2;
And although it does fetch the correct custom_name
s, because of the WHERE U.administration_id = 1;
it only fetches rows from used_mapping
where that condition is met. In other words, in this example it fetches
| id | name | customName |
|----|--------------|-------------------|
| 1 | Mapping 1 | Bar revenue |
| 3 | Mapping 3 | Cost of bar sales |
| 4 | Mapping 4 | Wages |
rather than
| id | name | customName |
|----|-----------|-------------------|
| 1 | Mapping 1 | Bar revenue |
| 2 | Mapping 2 | null |
| 3 | Mapping 3 | Cost of bar sales |
| 4 | Mapping 4 | Wages |
| 5 | Mapping 5 | null |
I've got it working with a slightly more complex LEFT JOIN
;
SELECT id,
name,
custom_name
FROM mapping
AS M
LEFT JOIN (
SELECT custom_name,
mapping_id
FROM used_mappings
WHERE administration_id = 2
) AS U ON U.mapping_id = M.id;
but it takes twice as long compared to fetching all rows from mapping
(obviously because for every record in mapping
, it runs a query on used_mappings
as well). Hence my question, is there a way of making the query more efficient?