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 Answer 1

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, 2019 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, 2019 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, 2019 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, 2019 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, 2019 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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