Pat Pat - 5 months ago 10
SQL Question

SQL query to join 2 tables using in values, split using comma

I have 2 table having values

Master_table

id refer_id value1 value2
1 C_1,C_2 x y
2 C_3,C_4 Z W
3 C_6 O P
4 C_4,C-2 Q R
5 C_1,C-3 S T


Secondary_table

id value3
C_1 A
C_2 B
C_3 C
C_4 D
C_5 E
C-6 F


Here i should combine both the tables and use refer_id of master table and refer it to the secondary table to get values as below

value1 value2 value3
X Y A
X Y B
Z W C
Z W D
O P F
Q R D
Q R B


I used the below query to get distinct and split the refer_id from the master table, but i didnt know how to retrive it
from secondary table

distinct(TRIM(regexp_substr(refer_id,'[^,]+', 1, level))) from Master_table connect by regexp_substr(refer_id, '[^,]+', 1, level) is not null;

MT0 MT0
Answer

Oracle Setup:

CREATE TABLE Master_table ( id, refer_id, value1, value2 ) AS
SELECT 1, 'C_1,C_2', 'x', 'y' FROM DUAL UNION ALL
SELECT 2, 'C_3,C_4', 'Z', 'W' FROM DUAL UNION ALL
SELECT 3, 'C_6',     'O', 'P' FROM DUAL UNION ALL
SELECT 4, 'C_4,C_2', 'Q', 'R' FROM DUAL UNION ALL
SELECT 5, 'C_1,C_3', 'S', 'T' FROM DUAL;

CREATE TABLE Secondary_table ( id, value3 ) AS
SELECT 'C_1', 'A' FROM DUAL UNION ALL
SELECT 'C_2', 'B' FROM DUAL UNION ALL
SELECT 'C_3', 'C' FROM DUAL UNION ALL
SELECT 'C_4', 'D' FROM DUAL UNION ALL
SELECT 'C_5', 'E' FROM DUAL UNION ALL
SELECT 'C_6', 'F' FROM DUAL;

Query:

SELECT value1, value2, value3
FROM   master_table m
       INNER JOIN
       secondary_table s
       ON ( ',' || m.refer_id || ',' LIKE '%,' || s.id || ',%' );

Output:

VALUE1 VALUE2 VALUE3
------ ------ ------
x      y      A      
x      y      B      
Z      W      C      
Z      W      D      
O      P      F      
Q      R      B      
Q      R      D      
S      T      A      
S      T      C  

Or you could use collections:

Oracle Setup:

A short helper type and function to split a string into its delimited values:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/

CREATE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
  p_result       stringlist := stringlist();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Query

SELECT value1, value2, value3
FROM   master_table m
       INNER JOIN
       secondary_table s
       ON ( s.id MEMBER OF split_String( m.refer_id ) );

(Same output as above.)

Comments