say, I have 3 tables : Toys, Baby, Diapers.
one Baby can have zero or many Toys and zero or many Diapers as well. (Toy - M:1 - Baby - 1:M - Diaper)
now in the report I am trying to put together Count of Toys per Baby, showing also Diapers Descr, when available.
if I drag Baby Name first, then Diaper Descr and then right after that I drop Count of Toys - I get only result set for data with Diapers and Toys present, and data where Diapers=null is not in the report!
in the resulting query I get subselect A (Baby + Diaper) joined to subselect B (Baby + Toy) on Baby ID (which is OK) and Diaper Description!!! which is NOT OK, because all data about Toys for null Diapers gets cut off
if, however, I drag Diaper first, then Baby, then Count of Toys - I get full result set even for Babies without Diapers. In the resulting queries I have subselects too, but they are joined on BabyID only.
Why is the order of columns that we drag changes the query so drastically?
How can we control that?
(Note: Diapers and Toys are named queries which are full outer joined to Baby for all BabyIDs, so even if Baby doesn't have Diaper - there will be an entry in Diapers view with Diaper Descr = null)