Retrieve data from another table if there is a matching value - else return based on first join cond-CodePudding

I am trying to fetch data from two tables by joining them on a primary column. However, if the secondary and tertiary column are available in the right table then consider them and return its corresponding column.

LEFT_TAB
USER  PRIMARY  SECONDARY  TERTIARY
1       A1       B1        (null)
2       X1     (null)      (null)

RIGHT_TAB
PRIMARY SECONDARY TERTIARY INDICATOR
  A1       B1      (null)    I1
  A1     (null)    (null)    I2
  X1     (null)    (null)    I3

Expected Output:
USER  PRIMARY  SECONDARY  TERTIARY INDICATOR
  1     A1         B1      (null)    I1
  2     X1       (null)    (null)    I3

I have tried INNER JOIN on PRIMARY as that's the primary column and SECONDARY & TERTIARY are optional. If they're available then we use them and corresponding rows (LEFT OUTER JOIN) but it returns duplicate.

select lft.user_id,lft.primary,lft.secondary,lft.tertiary,rit.indication
from left_tab lft
INNER JOIN right_tab rit ON left.primary = rit.primary
LEFT JOIN right_tab rite ON
(left.secondary = rite.secondary OR left.tertiary = rite.tertiary) ---(join that's bringing dups)
;

OUTPUT:
USER  PRIMARY  SECONDARY  TERTIARY INDICATOR
  1     A1         B1      (null)    I1
  1     A1         B1      (null)    I2 --duplicate
  2     X1       (null)    (null)    I3

CodePudding user response：

From Oracle 12, you can use:

SELECT l.user_id,
       l.primary,
       l.secondary,
       l.tertiary,
       r.indicator
from   left_tab l
       CROSS JOIN LATERAL (
         SELECT r.*
         FROM   right_tab r
         WHERE  l.primary = r.primary
         AND    (l.secondary = r.secondary OR r.secondary IS NULL)
         AND    (l.tertiary = r.tertiary OR r.tertiary IS NULL)
         ORDER BY (CASE WHEN r.secondary IS NOT NULL THEN 1 ELSE 0 END
                    CASE WHEN r.tertiary  IS NOT NULL THEN 1 ELSE 0 END) DESC
         FETCH FIRST ROW ONLY
       ) r;

Which, for the sample data:

CREATE TABLE LEFT_TAB (USER_ID, PRIMARY, SECONDARY, TERTIARY) AS
SELECT 1, 'A1', 'B1', CAST(null AS VARCHAR2(2)) FROM DUAL UNION ALL
SELECT 2, 'X1', null, null FROM DUAL UNION ALL
SELECT 3, 'Y1', 'Y2', 'Y3' FROM DUAL;

CREATE TABLE RIGHT_TAB (PRIMARY, SECONDARY, TERTIARY, INDICATOR) AS
SELECT 'A1', 'B1', CAST(null AS VARCHAR2(2)), 'I1' FROM DUAL UNION ALL
SELECT 'A1', null, null, 'I2' FROM DUAL UNION ALL
SELECT 'X1', null, null, 'I3' FROM DUAL UNION ALL
SELECT 'Y1', null, 'Y3', 'I4' FROM DUAL UNION ALL
SELECT 'Y1', 'Y2', 'Y3', 'I5' FROM DUAL UNION ALL
SELECT 'Y1', 'Y2', null, 'I6' FROM DUAL;

Outputs:

USER_ID PRIMARY SECONDARY TERTIARY INDICATOR

1 A1 B1 I1

2 X1 I3

3 Y1 Y2 Y3 I5

USER_ID	PRIMARY	SECONDARY	TERTIARY	INDICATOR
1	A1	B1		I1
2	X1			I3
3	Y1	Y2	Y3	I5

db<>fiddle here