I'm trying to build some editor server code from the following sql query:
SELECT A.ServiceName As Res1, 'Global list' As Res2, '0' As Res3, A.ID As Res4 FROM ServiceLists AS A WHERE A.CustomerIndex = 0
UNION ALL SELECT A.ServiceName As Res1, C.CustomerNumber As Res2, C.CustID As Res3, A.ID As Res4 FROM ServiceLists AS A
INNER JOIN Customers AS C ON A.CustomerIndex = C.CustID WHERE C.Dealer = 1
How do I build the server code? Here's what I have in the where/leftjoin parts of the server code:
editor.Where(q => q.Where("ServiceLists.CustomerIndex", 0).OrWhere("ServiceLists.CustomerIndex", lngCustIdx) ); editor.Where("Customers.Dealer", lngDistIdx); editor.LeftJoin("DistributorCommissions", "ServiceLists.id", "=", "DistributorCommissions.ByItemPOSResidualServiceID"); editor.LeftJoin("Customers", "ServiceLists.CustomerIndex", "=", "Customers.CustID");
How do I include the Union and build the condition where the records that need showing are where CustomerIndex = 0 and where ServiceLists shows records where dealer ID 1 has customers present in ServiceLists.