I'm sure this question has come up before and I'm almost embarrassed for asking it. But it's baffling me.
Suppose I have a table of companies (C1, C2, C3, C4, C5, C6) and a second table, mapping companies to head office.
e.g.
Company Head office
C1 HO1
C2 HO1
C3 HO2
C4 HO1
C4 HO2
Note that a company can map to more than one head office in my example. Also, C5 and C6 have no head office.
If I create a table with two columns, Company and Head office, then I'll get a nice list of the mappings, and there will be a '-' for the missing data against C5 and C6. Now, if I filter on Head office by choosing NOT HO2, then the result will only show me HO1. C5 and C6 will no longer be included because there are no associated records in the HO table.
On one hand this makes sense to me, but on the other, surely filtering NOT HO2 should also include those records for which there is no head office. Hopefully, the example attached might make more sense.
One thing I could do is create a mapping for all the companies without a head office (e.g. add C5 and C6 to the map, but call the head office "NA" or something. This would work, I'm sure, but in my live example, there are close to 100,000 rows of data and I'm reluctant to generate so many extra rows if there's a better way of doing it.
Hope this makes sense.
Thanks
James
