2
\$\begingroup\$

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_names, 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?

\$\endgroup\$
1
\$\begingroup\$
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;

The problem is in the where statement. Here it checks whether the administration_id = 2. For mappings where there is no match in used_mapping this id will be null. So it gets filtered out. Essentially you're performing an inner join here.

Instead you can also allow rows that have administration_id is null:

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 or U.administration_id IS NULL;
\$\endgroup\$
5
  • \$\begingroup\$ The problem is that U.administration_id can't be null. Used_mapping holds the mappings used by each administration, so if a mapping isn't used by a certain administration, it simply doesn't appear in used_mapping. \$\endgroup\$ – Alex Nov 28 '19 at 8:47
  • \$\begingroup\$ @Alex it can't be null in the table, it can be after the left join: rows in mapping that don't exist in used_mapping (2 and 5), won't have a matching record in the result set after the join, so there the columns coming from the used_mapping table will be null. Try executing select * from mapping M left join used_mapping U on U.mapping_id = M.id: you'll see what I mean. \$\endgroup\$ – JAD Nov 28 '19 at 8:51
  • \$\begingroup\$ Hmm, oddly your query works in a sample database I quickly set up, with limited records, but doesn't work in the actual database despite the structure being the same. \$\endgroup\$ – Alex Nov 28 '19 at 9:48
  • \$\begingroup\$ @Alex at risk of turning this into a SO-debug session, can you describe how it doesn't work? \$\endgroup\$ – JAD Nov 28 '19 at 10:23
  • \$\begingroup\$ I just realised the error, some mappings are simply not used by any administration ever, so they won't appear when running the query. Your query works in case all records from one column are used in the other, but that's not the case for me sadly. Basically, there's nothing wrong with your query, it's just not usable in my situation \$\endgroup\$ – Alex Nov 28 '19 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.