Union query

Union query

dynasoftdynasoft Posts: 422Questions: 67Answers: 3

Hi

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.

Thanks.

Answers

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    The code I included actually works fine. Thanks.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    edited March 2020

    If your conditions are really static (CustomerIndex = 0 and dealer ID = 1) then it is really easy: Just put your query into an SQL view and query the view with Editor.

    Editor can't do UNION, INNER JOIN etc. In an options instance you can't even use a LEFT JOIN as far as I remember.

    If you need something more complex you can either use views or other work arounds like your own proprietary queries inside a getFormatter etc. Views are the best option in my opinion.

    CREATE VIEW your_view AS
    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
    
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin

    You can do a left Join with the Editor libraries, but not UNIONS or a RIGHT JOIN. For that, as you say a VIEW would need to be used.

    Allan

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    @allan, LEFT JOIN yes, but not in an options instance, right? Or has that changed?
    https://editor.datatables.net/manual/php/joins

  • dynasoftdynasoft Posts: 422Questions: 67Answers: 3

    Thank you.

This discussion has been closed.