Left outer join eliminating 2 rows instead of inserting NULL

Question

If a time is missing I want NULL in that row/column instead of it not showing at all. I've used left outer join several times before and haven't had an issue.

If I start with the table with the 4 statuses:

SELECT Status FROM flight;
Status
OUT
OFF
ON
IN

So far so good. Now I want to left outer join the table with all the data

SELECT [Status], [time] FROM flight
left outer join FlightTime on (FlightTime.StatusId=flight.FlightTimeStatusId)
Status   Time
OUT      2020-03-10 19:23:00.000
IN       2020-03-10 22:15:00.000

Desired Result:

Status   Time
OUT      2020-03-10 19:23:00.000
OFF      NULL
ON       NULL
IN       2020-03-10 22:15:00.000

Sample OOOI data

Flight OUT                      OFF                     ON                      IN
0001   2020-02-07 15:15:00.000  2020-02-07 15:53:00.000 2020-02-07 20:17:00.000 2020-02-07 20:20:00.000
0002   2020-02-14 16:19:00.000  2020-02-14 16:29:00.000 2020-02-14 21:06:00.000 2020-02-14 21:08:00.000

Don't worry about the columns instead of rows. I will figure out how to pivot it later. This sample data is from an earlier query that works fine (and provides column data as desired) as long as no data is missing. The problem (as seen here) is when data is missing.

I have included sample data. I cannot include the schema as it's not my database. — Adrian, Mar 30 '20 at 20:02
Your `on` condition uses columns you haven't provided: `StatusId` and `FlightTimeStatusId`. Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Mar 30 '20 at 20:15
Do you reference FlightTime.[time] in your where clause? If you do, that will turn your left join into an inner join. — Isaac, Mar 31 '20 at 00:41

score 0 · Accepted Answer · answered Mar 31 '20 at 15:32

You can actually do this by using PIVOT. In your case when you PIVOT to fixed columns, if there is no data, it should return NULL.

Check this page to get help with PIVOT: Efficiently convert rows to columns in sql server

Select * from (
Your query that returns all the data including the [Time] column here
)  f
PIVOT
( 
    max([Time]) for Status in ([OUT], [OFF], [ON], [IN])
) piv;

Left outer join eliminating 2 rows instead of inserting NULL

1 Answers1