I have tables with the following relationships:
Customer
Setting
User.CustomerID
UserSetting.{UserID,SettingID}
DefaultSetting.{CustomerID,SettingID}
I am trying to create a typed list to join UserSetting, DefaultSetting, and Setting, using weak relations, to represent a union of all possible SettingIDs. In other words:
Setting LEFT JOIN UserSetting ON Setting.SettingID=UserSetting.SettingID
LEFT JOIN User ON UserSetting.UserID=User.UserID
LEFT JOIN DefaultSetting ON User.CustomerID=DefaultSetting.CustomerID AND Setting.SettingID=DefaultSetting.SettingID
The problem is, no matter what order I add the tables to the typed list, it will only join DefaultSetting to Setting via SettingID; it will not allow me to specify that DefaultSetting should be joined to User as well.
I have a feeling that this query is too complex to be handled with a typed list, so as a workaround I plan to use two different typed lists to retrieve DefaultSettings separately from UserSettings. However, I was curious if I was overlooking something in LLBLGen that would allow me to do what I am after.