Query equivalent to a left single match join that only returns one record per key match.
Article ID: KB0079991
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | All Versions |
Description
Query equivalent to a left single match join that only returns one record per key match.
Issue/Introduction
Query equivalent to a left single match join that only returns one record per key match.
Resolution
Resolution:
In a Left Single Match join, data will be kept (and columns added) only for rows that are available in the current data table. If additional rows exist in the new data, they will not be added to the current data table. If there are duplicates of the identifiers, only one value from the new data will be kept and added to the existing rows. You can achieve a left single match join using the following query, which uses an inner query to extract unique values from tableB, then applies a LEFT join on tableA and the new inner table.
In a Left Single Match join, data will be kept (and columns added) only for rows that are available in the current data table. If additional rows exist in the new data, they will not be added to the current data table. If there are duplicates of the identifiers, only one value from the new data will be kept and added to the existing rows. You can achieve a left single match join using the following query, which uses an inner query to extract unique values from tableB, then applies a LEFT join on tableA and the new inner table.
SELECT TableA.ColA, TableA.ColB, TableC.ColE
FROM TableA
LEFT JOIN (SELECT DISTINCT (ColA) as ColD, FIRST_VALUE(ColC) OVER
(PARTITION BY ColA) as ColE from TABLEB) TableC
ON TableA.ColA = TableC.ColD
ORDER BY ColA, ColB;
Feedback
Yes
No
Powered by
