Purpose
The SQL pattern retrieves distinct values from the first table where there are no matching entries in the second table.
Pattern
Each item in the first group is checked, and those that don't have a match in the second group are kept.
Parameters
- TBL1: This table contains the primary dataset.
- KEY1: This is the unique identifier for each record in tbl1, ensuring that each entry can be distinctly referenced.
- TBL2: This table holds a secondary dataset that may contain related records.
- KEY2: This is the unique identifier for each record in tbl2, allowing for distinct referencing of entries within this table.
Usage
In this use case, we have a table with clients and a table with transactions. We apply the REF pattern to get all client ids that made a transaction but are not in the client database.
Dataset
TRANSACTIONS
CLIENTS
Parameter
- TBL1 = [DQ-REF.Transactions$]
- KEY1 = CLIENT_ID
- TBL2 = [DQ-REF.Clients$]
- KEY2 = CLIENT_ID
Statement
Output
