Subquery issue with null records in left table

Question

Running into a bit of a problem.

SELECT
Q1.LOCATION_ID,
Q1.LAST_DATE,
Q1.SHIPMENT_LINE_STATUS_CODE
FROM Q1
WHERE
Q1.LAST_DATE =
(   SELECT
    MAX(Q2.LAST_DATE)
    FROM
    Q2
    WHERE
    Q2.LOCATION_ID = Q1.LOCATION_ID
    AND
    Q2.SHIPMENT_LINE_STATUS_CODE = 'RECEIVED'
)

The problem is if there is no "RECEIVED; on the SHIPMENT_LINE_STATUS_CODE, I get no records.

For example, I want to show the following:

LOCATION_ID   LAST_UPDATE_DATE     SHIPMENT_LINE_STATUS_CODE
4544                 4/1/2021                       RECEIVED
4876                 NULL                            NULL

However, I only get this:

LOCATION_ID   LAST_UPDATE_DATE     SHIPMENT_LINE_STATUS_CODE
4544                 4/1/2021                       RECEIVED

I need to return the records that are NULL when the SHIPMENT_LINE_STATUS_CODE is not 'RECEIVED'

Answer 1

You could rewrite this using a LEFT JOIN which would return the results from Q1 and matching results only from Q2:

SELECT  Q1.LOCATION_ID,
        Q1.LAST_DATE,
        Q1.SHIPMENT_LINE_STATUS_CODE
  FROM  Q1
    LEFT JOIN (
              SELECT  LOCATION_ID,
                      MAX(LAST_DATE) AS LAST_DATE
                FROM  Q2
                WHERE SHIPMENT_LINE_STATUS_CODE = 'RECEIVED'
                GROUP BY LOCATION_ID
              ) a ON a.LOCATION_ID = Q1.LOCATION_ID AND a.LAST_DATE = Q1.LAST_DATE