---
title: "Lusid.Relationship"
slug: "lusidrelationship"
updated: 2024-08-21T12:29:22Z
published: 2024-08-21T12:29:22Z
canonical: "support.lusid.com/lusidrelationship"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Lusid.Relationship

| **Type** | **Read/write** | **Author** | **Availability** |
| --- | --- | --- | --- |
| [Data provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider) | Read | Finbourne | Provided with LUSID |

The `Lusid.Relationship` provider enables you to write a [Luminesce SQL query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that retrieves relationships for one or more [entities that support relationships](/v1/docs/creating-relationships-between-certain-types-of-entity) in LUSID.

> **Note:** The LUSID user running the query must have sufficient [access control permissions](/v1/docs/creating-policies-to-control-access-to-providers-for-different-luminesce-end-users) to both use the provider *and* read relationship data stored in LUSID. This should automatically be the case if you are the domain owner.

`Lusid.Relationship` can query a relationship from either end. For example, if portfolio entities are related to person entities, then you can either look up a particular person and get related portfolios, or look up a particular portfolio and get related persons. Note by default you only retrieve simple identifiers for the entity at the other end, but you can join to the provider for that entity in order to retrieve more useful information (see examples below).

**See also:** [Lusid.Relationship.Writer](/v1/docs/lusidrelationshipwriter)

## Basic usage

```sql
@lookup_table = <select-statement>;
select * from Lusid.Relationship where toLookUp = @lookup_table and <filter-expression>
```

## Query parameters

`Lusid.Relationship` has parameters that help you construct a valid table of data to look up.

The `toLookUp` parameter is mandatory. To look up an entity and retrieve its relationships, you require an *identifier* for that entity. Note that identifiers for different entity types have different components:

| **Entity type** | **Components of identifier** |
| --- | --- |
| `LegalEntity`, `Person` | `idTypeScope`, `idTypeCode`, `code` |
| `Instrument` | `identifierType`, `identifierValue`, `scope` (if not in the `default` scope) |
| Custom entity | `identifierType`, `identifierScope`, `identifierValue` |
| All other types, including `Portfolio` and `PortfolioGroup` | `scope`, `code` |

For more information, consult [this table of entities and their identifiers](/v1/docs/what-is-an-entity).

Use the following data fields in the `select` statement for `toLookUp` to identify the entity to look up:

| **Data field** | **Status** | **Explanation** |
| --- | --- | --- |
| `EntityType` | Required | The type of entity to look up. Must be a string that is one of the entity types supporting relationships, for example `Portfolio`, `PortfolioGroup`, `LegalEntity`, `Person`, `Instrument` or the name of a [custom entity](/v1/docs/extending-lusids-data-model-using-custom-entities) preceded by `~`, for example `~Office`. |
| `EntityScope` | Required for all except `Instrument`. If omitted for `Instrument`, uses the `default` instrument scope. | Part of the identifier for the entity to look up. The origin of this value depends on the entity type: \| **Entity type** \| **Origin of field value** \| \| --- \| --- \| \| `LegalEntity`, `Person` \| `idTypeScope` \| \| `Instrument` (optional) \| `scope` \| \| Custom entity \| `identifierScope` \| \| All other types, including `Portfolio` and `PortfolioGroup` \| `scope` \| |
| **Entity type** | **Origin of field value** |
| `LegalEntity`, `Person` | `idTypeScope` |
| `Instrument` (optional) | `scope` |
| Custom entity | `identifierScope` |
| All other types, including `Portfolio` and `PortfolioGroup` | `scope` |
| `EntityCode` | Required | Part of the identifier for the entity to look up. The origin of this value depends on the entity type. \| **Entity type** \| **Origin of field value** \| \| --- \| --- \| \| `LegalEntity`, `Person` \| `idTypeCode` \| \| `Instrument` \| `identifierType` \| \| Custom entity \| `identifierType` \| \| All other types, including `Portfolio` and `PortfolioGroup` \| `code` \| |
| **Entity type** | **Origin of field value** |
| `LegalEntity`, `Person` | `idTypeCode` |
| `Instrument` | `identifierType` |
| Custom entity | `identifierType` |
| All other types, including `Portfolio` and `PortfolioGroup` | `code` |
| `EntityValue` | Required for `LegalEntity`, `Person`, `Instrument` and custom entities. Do not specify for other types. | Part of the identifier for the entity to look up. The origin of this value depends on the entity type: \| **Entity type** \| **Origin of field value** \| \| --- \| --- \| \| `LegalEntity`, `Person` \| `code` \| \| `Instrument` \| `identifierValue` \| \| Custom entity \| `identifierValue` \| \| All other types, including `Portfolio` and `PortfolioGroup` \| N/A. Do not specify this field. \| |
| **Entity type** | **Origin of field value** |
| `LegalEntity`, `Person` | `code` |
| `Instrument` | `identifierValue` |
| Custom entity | `identifierValue` |
| All other types, including `Portfolio` and `PortfolioGroup` | N/A. Do not specify this field. |
| `RelationshipScope`, `RelationshipCode` | Optional | The `scope` and `code` of a particular relationship type. If omitted, relationships of every type are returned, though note this may take some time. |

To list all parameters, their data types, default values, and an explanation for each, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

```sql
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Relationship' and FieldType = 'Parameter';
```

## Data fields

By default, `Lusid.Relationship` returns a table of data populated with particular fields (columns). You can return a subset of these fields.

> **Note**: Some of these fields are mandatory to specify in your query; see the section above.

To list all fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

```sql
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Relationship' and FieldType = 'Column';
```

> **Note:** Fields marked 'main' are returned by queries that start `select ^ from Lusid.Relationship...`

## Errors

We recommend examining the results of every query using the `Error` field.

This field is populated if `Lusid.Relationship` cannot retrieve an entity, or relationships for that entity, for some reason. For example, the `Error` field reveals that this query:

```sql
@lookup_table = select 'Portfolio' as EntityType, 'UK-Equities' as EntityCode;
select Error from Lusid.Relationship where toLookUp = @lookup_table;
```

...fails because `EntityScope` has not been provided in the lookup table.

## Examples

> **Note**: For more examples, try the [Luminesce Github repo](https://github.com/finbourne/luminesce-examples/tree/master/examples).

### Example 1: Retrieve all relationships for a particular portfolio

```sql
@lookup_table = select 'Portfolio' as EntityType, 'Finbourne-Examples' as EntityScope, 'Global-Equity' as EntityCode;
select * from Lusid.Relationship where toLookUp = @lookup_table;
```

### Example 2: Retrieve relationships of a particular type for a particular person

Note this query assumes `PortManId` is an identifier that has been [explicitly 'inlined' as a field](/v1/docs/lusidperson#prerequisites) for the `Lusid.Person` provider.

```sql
@lookup_table = select 'Person' as EntityType, 'PortfolioManagers' as EntityScope, 'ManagerId' as EntityCode,
'PortMan1' as EntityValue, 'PortfolioManagementTeam' as RelationshipScope, 'Managers' as RelationshipCode;

select * from Lusid.Relationship where toLookUp = @lookup_table;
```

### Example 3: Retrieve more information about people related to a particular portfolio

This query:

- Looks up all the relationships belonging to a Global Equity portfolio.
- Joins `Lusid.Relationship` to `Lusid.Person` to retrieve more information about related people.
- Displays the `DisplayName` and `LusidEntityId` of related people.

```sql
@lookup_table = select 'Portfolio' as EntityType, 'Finbourne-Examples' as EntityScope, 'Global-Equity' as EntityCode;
@relationships = select * from Lusid.Relationship where toLookUp = @lookup_table;

select 
    r.*,
    p.DisplayName, p.LusidEntityId
from @relationships r
left join Lusid.Person p
    on r.RelatedEntityValue = p.PortManId
```

### Example 4: Retrieve all the portfolios related to two legal entities

In this example:

- The `Lusid.LegalEntity` provider is used to populate values for `EntityValue` in the lookup table, and its `where` clause selects the two legal entities to look up.
- More information is retrieved about related portfolios by joining `Lusid.Relationship` to `Lusid.Portfolio` on the related entity scope.

```sql
@lookup_table = select 'LegalEntity' as EntityType, 'InternalIdentifier' as EntityScope, 'Id1' as EntityCode,
Value as EntityValue, 'Production' as RelationshipScope, 'PortfolioLe' as RelationshipCode from Lusid.LegalEntity where Value in ('IdA', 'IdB');

@relationships = select * from Lusid.Relationship where toLookUp = @lookup_table;

select
    r.EntityType, r.EntityScope, r.EntityCode, r.EntityValue,
    p.*
from @relationships r
left join Lusid.Portfolio p
    on r.RelatedEntityScope = p.PortfolioScope
    and r.RelatedEntityCode = p.PortfolioCode
```

### Example 5: Retrieve all the legal entities related to UK portfolios

In this example the `Lusid.Porfolio` provider is used to populate values in the lookup table, and the `Lusid.LegalEntity` provider is used to retrieve more information about related legal entities.

```sql
@lookup_table = select 'Portfolio' as EntityType, PortfolioScope as EntityScope, PortfolioCode as EntityCode, 
'Production' as RelationshipScope, 'PortfolioLe1' as RelationshipCode from Lusid.Portfolio where PortfolioCode like 'UK-%';

@relationships = select * from Lusid.Relationship where toLookUp = @lookup_table;

select
    r.EntityType, r.EntityScope, r.EntityCode, r.EntityValue,
    legal.*
from @relationships r
inner join Lusid.Legalentity legal
    on (r.RelatedEntityCode = 'Id1' and r.RelatedEntityValue = legal.IdA)
```