# Querying all records from one table, add column from another with foreign key constraint

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;

| 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


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
) 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?

SELECT  M.id,
M.name,
U.custom_name as customName
FROM    mapping M


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

• 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. – Alex Nov 28 '19 at 8:47
• @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. – JAD Nov 28 '19 at 8:51