Create a Query for Customer Records and Run It as a Non-Paged Query

The following sample creates a query for customer records, joins the query with two other query types, and runs the query.

/**
 * @NApiVersion 2.1
 */
require(['N/query'], query => {
  // Create a query definition for customer records
  let myCustomerQuery = query.create({
    type: query.Type.CUSTOMER
  });

  // Join the original query definition based on the salesrep field. In a customer
  // record, the salesrep field contains a reference to an employee record. When you
  // join based on this field, you are joining the query definition with the employee
  // query type, and you can access the fields of the joined employee record in
  // your query.
  let mySalesRepJoin = myCustomerQuery.autoJoin({
    fieldId: 'salesrep'
  });

  // Join the joined query definition based on the location field. In an employee
  // record, the location field contains a reference to a location record.
  let myLocationJoin = mySalesRepJoin.autoJoin({
    fieldId: 'location'
  });

  // Create conditions for the query
  let firstCondition = myCustomerQuery.createCondition({
    fieldId: 'id',
    operator: query.Operator.EQUAL,
    values: 107
  });
  let secondCondition = myCustomerQuery.createCondition({
    fieldId: 'id',
    operator: query.Operator.EQUAL,
    values: 2647
  });
  let thirdCondition = mySalesRepJoin.createCondition({
    fieldId: 'email',
    operator: query.Operator.START_WITH_NOT,
    values: 'example'
  });

  // Combine conditions using and() and or() operator methods. In this example,
  // the combined condition states that the id field of the customer record must
  // have a value of either 107 or 2647, and the email field of the employee
  // record (the record that is referenced in the salesrep field of the customer
  // record) must not start with 'example'.
  myCustomerQuery.condition = myCustomerQuery.and(
    thirdCondition, myCustomerQuery.or(firstCondition, secondCondition)
  );

  // Create query columns
  myCustomerQuery.columns = [
    myCustomerQuery.createColumn({
      fieldId: 'entityid'
    }),
    myCustomerQuery.createColumn({
      fieldId: 'id'
    }),
    mySalesRepJoin.createColumn({
      fieldId: 'entityid'
    }),
    mySalesRepJoin.createColumn({
      fieldId: 'email'
    }),
    mySalesRepJoin.createColumn({
      fieldId: 'hiredate'
    }),
    myLocationJoin.createColumn({
      fieldId: 'name'
    })
  ];

  // Sort the query results based on query columns
  myCustomerQuery.sort = [
    myCustomerQuery.createSort({
      column: myCustomerQuery.columns[3]
    }),
    myCustomerQuery.createSort({
      column: myCustomerQuery.columns[0],
      ascending: false
    })
  ];

  // Run the query
  let resultSet = myCustomerQuery.run();

  // Retrieve and log the results
  let results = resultSet.results;
  for (let i = results.length - 1; i >= 0; i--)
    log.debug(results[i].values);
  log.debug(resultSet.types);
});