Class Join

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents a SQL JOIN operation between two tables or between a table and another join.

public class Join : IFromItem

Inheritance: object

Join

Implements: IFromItem

Examples

Simple INNER JOIN with same column name:

// Join orders with users on user_id
Join join = new Join(
    ordersTable,                    // Left table
    usersTable,                     // Right table
    "user_id",                      // Column name (same in both tables)
    JoinType.Inner                  // Join type
);

Query query = new Query();
query.AddFromItem(join);
query.AddSelectItem(usersTable["username"]);
query.AddSelectItem(ordersTable["order_date"]);
query.AddSelectItem(ordersTable["total"]);

// SQL: SELECT users.username, orders.order_date, orders.total
//      FROM orders
//      INNER JOIN users ON orders.user_id = users.user_id

JOIN with different column names:

// Join orders.customer_id with users.id
JoinCondition joinCondition = new JoinCondition(
    ordersTable["customer_id"],
    usersTable["id"]
);

Join join = new Join(
    ordersTable,
    usersTable,
    joinCondition,
    JoinType.Left
);

// SQL: SELECT * FROM orders
//      LEFT JOIN users ON orders.customer_id = users.id

Multiple chained joins:

// Join: orders -> users -> addresses
JoinCondition userJoin = new JoinCondition(
    ordersTable["user_id"],
    usersTable["id"]
);

JoinCondition addressJoin = new JoinCondition(
    usersTable["address_id"],
    addressesTable["id"]
);

// First join: orders with users
Join firstJoin = new Join(ordersTable, usersTable, userJoin, JoinType.Inner);

// Second join: add addresses to the previous join
Join secondJoin = new Join(firstJoin, addressesTable, addressJoin, JoinType.Left);

Query query = new Query();
query.AddFromItem(secondJoin);

// SQL: FROM orders
//      INNER JOIN users ON orders.user_id = users.id
//      LEFT JOIN addresses ON users.address_id = addresses.id

Self-join with aliases:

// Find employees and their managers
Table employees = schema["employees"];

JoinCondition managerJoin = new JoinCondition(
    employees["manager_id"],
    employees["id"]
);

Join join = new Join(
    employees,           // Left table
    employees,           // Right table (same table)
    managerJoin,
    JoinType.Left,
    "e",                 // Left table alias
    "m"                  // Right table alias
);

// SQL: FROM employees e
//      LEFT JOIN employees m ON e.manager_id = m.id

Remarks

Join combines rows from two tables based on a related column. Velocity supports all standard SQL join types: INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins.

Joins can be chained together to create multi-table queries. When chaining joins, each new Join references the previous join as its FromExpression.

Constructors

Join(Table, IFromItem, string, JoinType, string, string): Creates a new join using the same column name on both sides of the join. This is a convenience constructor for the common case where the join column has the same name in both tables.