Loading TOC...
XQuery op:join-left-outer

MarkLogic Server 11.0 Product Documentation
AccessPlan.prototype.joinLeftOuter

AccessPlan.prototype.joinLeftOuter(
   rightPlan as String,
   [keys as ondef | ondef[]],
   [condition as xs.boolean]
) as ModifyPlan

Summary

This method yields one output row set with the rows from an inner join as well as the other rows from the left row set.

The join performs natural joins between columns with the same identifiers. To prevent inadvertent natural joins, specify a different qualifier for the left or right columns or use different column names for the left and right columns.

Parameters
rightPlan The row set from the right view.
keys Equality condition(s) expressed using one or more calls to the function op.on. These conditions are used to compare the left and right rows.
condition A boolean expression used to compare the left and right rows. See Boolean Expression Functions for the list of functions used to build boolean expressions.

Usage Notes

Both keys and condition are used to specify the join condition. keys allows for a simplified syntax for equality join condition. While condition allows for inequality and complex join conditions. This method combines keys and condition using an AND operator such that: 
      view
        .joinLeftOuter(
          otherView, 
          op.on(view.col('column1'), otherView.col('column2')),
          op.ge(view.col('column3'), otherView.col('column4'))
        )
is the same as 
      view
        .joinLeftOuter(
          otherView,
          null,
          op.and(
            op.eq(view.col('column1'), otherView.col('column2')),
            op.ge(view.col('column3'), otherView.col('column4'))
          )
        )

joinLeftOuter is a method of the following classes:

Example

// Calculate the total expenses for each employee and return results in order of employee number.

const op = require('/MarkLogic/optic');

const employees = op.fromView('main', 'employees');
const expenses = op.fromView('main', 'expenses');
const totalexpenses  = op.col('totalexpenses');
const Plan =
employees.joinLeftOuter(expenses, op.on(employees.col('EmployeeID'), expenses.col('EmployeeID')))
   .groupBy(employees.col('EmployeeID'), ['FirstName', 'LastName', expenses.col('Category'),
    op.sum(totalexpenses, expenses.col('Amount'))])
   .orderBy('EmployeeID')
Plan.result();

Example

'use strict';
 
const op = require('/MarkLogic/optic');

const fruits = op.fromLiterals([
    {"fruit" : "mango", "count" : 10},
    {"fruit" : "apple", "count" : 7},
    {"fruit" : "orange", "count" : 9}
  ]);
const orders = op.fromLiterals([
    {"flavor" : "apple", "cups" : 5},
    {"flavor" : "orange", "cups" : 14}
  ]);

fruits.joinLeftOuter(
  orders,
  op.on(op.col('fruit'), op.col('flavor')),
  op.ge(op.col('count'), op.col('cups'))
)
.result()

/* 
  fruit of 'orange' and 'mango' will pair with flavor of 'null' 
  fruit of 'apple' will pair with flavor of 'apple' 
*/

Stack Overflow iconStack Overflow: Get the most useful answers to questions from the MarkLogic community, or ask your own question.