how can i join with an id number vs list string id

Question

I have table 1: with id is a number int,

Table 2 contain references id 1 like this: '1,2,3...', you can see more detail bellow:

WITH TB1 AS(
     SELECT 1 ID FROM DUAL UNION ALL
     SELECT 2 ID FROM DUAL UNION ALL
     SELECT 3 ID FROM DUAL
), TB2 AS
(
   SELECT 'A' NAMES, '1,2' IDREF FROM DUAL
)
SELECT t1.*, t2.Names FROM TB1 t1 join tb2 t2 on t1.id in t2.idref

How can i join tb1 vs tb2 by id in?

The correct solution would be to fix your data model. Do not store multiple comma separated values in a single column. — , Oct 26 '17 at 06:55
Thanks, I known, i has just selected, i'm not inserted database — A Good Boy, Oct 26 '17 at 07:14

score 1 · Accepted Answer · answered Oct 26 '17 at 06:15

You can join using the LIKE operator, with a small trick baked into the cake:

SELECT t1.*
FROM TB1 t1
INNER JOIN TB2 t2
    ON ',' || t2.IDREF || ',' LIKE '%,' || t1.ID || ',%'

Taking ID=1 for example, the idea here is to look for ,1, the following CSV string:

,1,2,

We can prepend and postpend a comma to the IDREF column, to ensure that every isolated ID value is delimited by a comma on both sides. Then, simply search for a given ,ID,, with commas on both sides.

Demo

Thanks, i has been solove problem! – A Good Boy Oct 26 '17 at 06:33 — A Good Boy, Oct 26 '17 at 06:33

score 1 · Answer 2 · answered Oct 26 '17 at 06:53

Try:

WITH TB1 AS(
     SELECT 1 ID FROM DUAL UNION ALL
     SELECT 2 ID FROM DUAL UNION ALL
     SELECT 3 ID FROM DUAL
), TB2 AS
(
   SELECT 'A' NAMES, '1,2' IDREF FROM DUAL
)                                                                                       
select T1.*, T2.*
from TB1 T1 
    join TB2 T2 on T1.id in (select regexp_substr(T2.IDREF,'[^,]+', 1, level) as list from dual
                                            connect by regexp_substr(T2.IDREF, '[^,]+', 1, level) is not null)

score 0 · Answer 3 · answered Oct 26 '17 at 09:10

Do not store the values as a string list.

Either use a collection and the MEMBER OF operator:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE IntList IS TABLE OF INTEGER

Query 1:

WITH TB1 ( ID ) AS (
     SELECT 1 FROM DUAL UNION ALL
     SELECT 2 FROM DUAL UNION ALL
     SELECT 3 FROM DUAL
),
TB2 ( names, idref ) AS (
   SELECT 'A', IntList( 1,2 ) FROM DUAL
)
SELECT t1.*,
       t2.Names
FROM   TB1 t1
       join tb2 t2
       on t1.id MEMBER OF t2.idref

Results:

| ID | NAMES |
|----|-------|
|  1 |     A |
|  2 |     A |

Or separate your list into its own table so that you can properly reference the foreign keys of TB1:

Query 2:

WITH TB1 ( ID ) AS (
     SELECT 1 FROM DUAL UNION ALL
     SELECT 2 FROM DUAL UNION ALL
     SELECT 3 FROM DUAL
),
TB2 ( names ) AS (
   SELECT 'A' FROM DUAL
),
TB3 ( names, idref ) AS (
   SELECT 'A', 1 FROM DUAL UNION ALL
   SELECT 'A', 2 FROM DUAL
)
SELECT t1.*,
       t2.Names
FROM   TB1 t1
       join tb3 t3
       on t1.id = t3.idref
       join tb2 t2
       on t2.names = t3.names

Results:

| ID | NAMES |
|----|-------|
|  1 |     A |
|  2 |     A |

how can i join with an id number vs list string id

3 Answers3

Demo