Query with multiple foreign keys to the same table I have a primary table, REP_ASSIST.ES_LOG with multiple columns that are foreign keys to another table, REP_ASSIST.ES_DOCUMENT_STATUSES. Here is an example of what my main table looks like:

+-------------------------+-----------+-------------------------+-----------------------+
|       created_at        | filing_id | prior_dd_rcvd_status_id | new_dd_rcvd_status_id |
+-------------------------+-----------+-------------------------+-----------------------+
| 2019-09-04 10:58:48.000 |       988 | 2                       |                     2 |
| 2019-09-04 10:47:03.000 |       988 | 1                       |                     2 |
| 2019-08-28 23:56:47.000 |       988 | null                    |                     1 |
+-------------------------+-----------+-------------------------+-----------------------+


Both the prior_dd_rcvd_status_id and new_dd_rcvd_status_id are foreign keys to the other table, a sample of which is here:

+------------------+--------------------+
| dd_doc_status_id | dd_doc_status_name |
+------------------+--------------------+
|                1 | RECEIVED           |
|                2 | MISSING            |
|                3 | NOT_NEEDED         |
|                4 | UNKNOWN            |
+------------------+--------------------+




I need to pull in the values from the lookup table for a printable report; right now I'm using a subquery to get the dd_doc_status_name as a user-friendly name instead of the dd_doc_status_id.

SELECT created_at,
       filing_id,
       (
           SELECT dd_doc_status_name
           FROM REP_ASSIST.ES_DOCUMENT_STATUSES
           WHERE ES_DOCUMENT_STATUSES.dd_doc_status_id = prior_dd_rcvd_status_id
       ) AS 'prior_dd_status',
       (
           SELECT dd_doc_status_name
           FROM REP_ASSIST.ES_DOCUMENT_STATUSES
           WHERE ES_DOCUMENT_STATUSES.dd_doc_status_id = new_dd_rcvd_status_id
       ) AS 'new_dd_status'
FROM REP_ASSIST.ES_LOG
WHERE filing_id = 988;


However, I'm concerned that using subqueries like this may be a bit of a hack and affect performance (especially if I end up adding additional columns that need this information).

I'm getting the correct results (see below), but I'd like to know if there is a more efficient and more standard way.

+-------------------------+-----------+----------------------+--------------------+
|       created_at        | filing_id | prior_dd_rcvd_status | new_dd_rcvd_status |
+-------------------------+-----------+----------------------+--------------------+
| 2019-09-04 10:58:48.000 |       988 | MISSING              | MISSING            |
| 2019-09-04 10:47:03.000 |       988 | RECEIVED             | MISSING            |
| 2019-08-28 23:56:47.000 |       988 | null                 | RECEIVED           |
+-------------------------+-----------+----------------------+--------------------+
I've updated the Fiddle with your initial query and 2 alternatives.

This is the initial query plan:



Using OUTER APPLY instead of the nested SELECT statements.

select created_at, prior_dd_rcvd_status_id, 
    new_dd_rcvd_status_id, prior_dd_status, new_dd_status, filing_id
from ES_LOG
outer apply (
    select dd_doc_status_name as prior_dd_status 
    from ES_DOCUMENT_STATUSES where dd_doc_status_id = prior_dd_rcvd_status_id
) a
outer apply (
    select dd_doc_status_name as new_dd_status 
    from ES_DOCUMENT_STATUSES where dd_doc_status_id = new_dd_rcvd_status_id
) b
where filing_id = 988;


The query plan gets simplified:



And using LEFT OUTER JOIN instead of the OUTER APPLY.

select created_at, prior_dd_rcvd_status_id, 
    new_dd_rcvd_status_id, 
    a.dd_doc_status_name as prior_dd_status, 
    b.dd_doc_status_name as new_dd_status, 
    filing_id
from ES_LOG
left outer join ES_DOCUMENT_STATUSES a 
    on a.dd_doc_status_id = prior_dd_rcvd_status_id
left outer join ES_DOCUMENT_STATUSES b 
    on b.dd_doc_status_id = new_dd_rcvd_status_id
where filing_id = 988;


With an even more simplified query plan:



However, as you can see, the table scans take most of the time. So I'm not sure how much performance you could gain by picking either of the alternatives.

Further improvements require the use of indexes (Table Scan vs Index Scan). Deciding which index to provide depends not only on this query, but also the general design of how these tables will be used for other queries and CRUD operations. I would advise you learn about them.