CODEWITHSUNDEEP
ms-accessnull
user1934868
user1934868

Reputation: 99

My MS Access query of a query is not returning rows that have a null value in a field

I have one query, called qry_sub_Loop, that uses a left join and has a null value in a derived field called Next_Home_Stop as a result of the left join.

I am having problems in other queries based off qry_sub_Loop.

As a test to isolate the problem I have tried:

  1. select * from qry_sub_Loop where Next_Home_Stop is null
  2. select * from qry_sub_Loop where Asset='C-5M-000001'

C-5M-000001 is an Asset that has some values in Next_Home_Stop in some rows and null in other rows

Query 1) does not return any records. Query 2) only returns the rows where Asset='C-5M-000001' and Next_Home_Stop is not null.

table: arrival
- MissionID (text) 
- ArrivalTime (double) 
- DepartureTime (double) 
- DepartureLocation (text) 
- ArrivalLocation (text) 
- Asset (text) 

table: aircraft
- Asset (text), 
- Home (text)

Query:

select
    aircraft.Home, aircraft.Asset, arrival.DepartureTime,
    min(arrival_1.ArrivalTime) as Next_Home_Stop,
    max(arrival_2.ArrivalTime) as LastStop
From
    arrival as arrival_1
    right join ((aircraft
                 inner join arrival 
                    on arrival.DepartureLocation = aircraft.Home and
                       arrival.Asset = aircraft.Asset)
          inner join arrival as arrival_2
             on arrival_2.Asset = arrival.Asset and
                arrival_2.ArrivalTime > arrival.DepartureTime) 
        on arrival_1.Asset = arrival.Asset and
           arrival_1.ArrivalTime > arrival.DepartureTime and
           arrival_1.ArrivalLocation = arrival.DepartureLocation
group by ...

Upvotes: 3

Views: 2135

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112372

I don't know if this applies to your query, but a common problem with outer joins occurs when you have a condition on the outer table

SELECT *
FROM
   A
   LEFT JOIN B
       ON A.X = B.X
WHERE
   B.Y = 'something'

This query will never yield empty rows for the table B, since the condition would not be fulfilled for them. Including the condition in the join condition does the trick:

SELECT *
FROM
   A
   LEFT JOIN B
       ON (A.X = B.X AND B.Y = 'something')

Now rows of table A will be returned even when no matching rows exist in table B.

UPDATE

The join condition

arrival.DepartureLocation = aircraft.Asset

is wrong. You should compare the DepartureLocation with another Location, not with an Asset

Upvotes: 1

HansUp
HansUp

Reputation: 97101

Examine the Next_Home_Stop values returned by qry_sub_Loop.

SELECT DISTINCT Next_Home_Stop FROM qry_sub_Loop;

Is a Null returned by that query? My guess is no, because you said query #2 returns values which are not Null. Because Next_Home_Stop is actually min(arrival_1.ArrivalTime), it can not include both Null and non-Null values.

Min(ArrivalTime) can return Null in only 2 situations:

  1. All ArrivalTime values are Null.
  2. There are now rows for Min() to evaluate.

If ArrivalTime includes any non-Null values, Min() will ignore the Null values and give you the minimum non-Null value.

Upvotes: 3

sourcecode
sourcecode

Reputation: 1802

Because the NULL values in the joined rows are placed into the result set at such a late stage in query processing, you cannot test them as NULL values within the query itself.

so for retrieving null values you can do like this

     SELECT * FROM qry_sub_Loop 
     where 
     Next_Home_Stop 
     NOT IN               //you have to explicitly look for null
     (select Next_Home_Stop from qry_sub_Loop );

Upvotes: 1

Related Questions