This may be a redd herring. I'm trying to track down a puzzling error in what looks like a simple SQL query.
Somebody noticed that a certain field was Null, so I went looking for why that was. The field was in a table that is essentially a materialized view. The view itself is nothing special, though a bit busy to show. It's basically a join between a parent table and a child table, with eight other joins in there to turn integer FKs into more human readable values for display. That's not exactly a complicated query, nor is there anything particularly special about this one. I could show all the joins, but there's nothing special about any of them. Left joins to the child table and the pick list tables, as there may not be any child records (and the parent table holds rather little data). The field with the Null values was one of those human readable fields, and it really can't be null if there are any child records.
In this case, there were 17 child records, and only two of them had nulls. Those two also happened to have the same value in the FK field. Naturally, that suggested that it was a value that no longer existed in the pic table. That would be simple...and I wouldn't be writing this.
Unfortunately, the first thing I did was ignore the materialized view and just create a query to look at an example. To do that, I built up a query with only those joins I needed to see the records. Also, since I knew that there would be child records for the parent record, I didn't bother with any left joins. This meant that the query was very simple indeed. In fact, it was now just two inner joins, one between the parent and child, and one to the one pic list that had the value in question. The problem is that this worked just fine. The value in the FK does, in fact, join to the pic list table and return the proper value. I got my 17 records, and they all had values in the field in question...and they were correct, too.
So, now I have a view that works just fine, though it only includes a couple fields, and a view that includes those fields along with a whole bunch of other fields. In both cases, one critical field displays a human readable value from a table. The joins are on the same tables in both fields, but one view returns a value for all 17 rows, while the other view returns 17 rows, but that one field only has values for 15 of them.
The only key difference that I have seen thus far is that the one view has TABLOCK HOLDLOCK, while the other does not. That doesn't seem like it should impact a join on one value, but I figured I'd put this out there just in case. The join is working, it's just that one value of the FK fails to return what it should.
I'll be trying other things, but any ideas would be welcome. How can a join on a table sometimes return a value for FK X, while in a different view, the join returns no value for FK X, though it returns values for all other FK values?

