Edit: fixed - Thanks! I do appreciate your help, by poking around trying to find the issue this time round it dawned on me to split the two separate Left Join routes with Table B & C in the original query and do Table A in the subsequent query, elegant enough,
Just for speed etc I've ended up 6 or 7 queries deep before, or a full screen of tables and joins! The joy of learning all this myself and through experimentation on the query builder and google :-D
I'm back on this problem as it still seems silly to me and having to bodge with a temp-table that I keep re-creating is having it's own down stream issues
I've re read your posts a lot over the past months and I really don't follow the issue with joins and null's to be honest, I've made a ton of left joins with nulls to nulls on fields that fairly definitely NULL over the years, they just work like joins work, all the time, except now
edit: this got confusing, I tried to clarify my data but the issue seems to be I have two left join trees and they don't like coexisting, I've basically got my block of inner join tables where all batches exist, but then additional info about those batches comes either from Table A or Tables B&C (table A holds a docID and Customer) table B holds DocID and CustID so then Table C is needed to get Customer from customer ID
so
Inner Joins -> LEft Join Table A
Inner Joins -> left join table B -> left join Table C
edit2 - to be specific:
As long as you aren't JOINing on a field that can align with a NULL you should be OK. Having a JOIN implies certain indexing requirements that can trip you up real fast.
I don't actually understand what you mean "JOINing on a field that can align with a NULL" or in your other post "but HOW you attempt the match-up. A JOIN is really sensitive to how you match it to another field."
you either drag and drop the arrow in designer or say "Left join tbl1.fldA = tbl2.fldA" I don't get "align" or "match-up" as you must have some deeper meaning here I'm missing?
sorry and the Where clause bit confuses me more, a Join is not a Where, I understand they're entirely different things, I've used a few unjoined table pairs to do cartesian product or theta joins on a few rare occasions