0

I have two ORACLE tables.

First table:

ID    l_name f_name belong_to

123   Smith  John        A Club

456   Jones  Mary        The Club, Your Club, My Club

789   Moore  Sue         The Club, A Club, Some Club

007   Wills  Art         My Club, Some Club, Two Club

Second table:

Club_ID  Name

1            A Club

2            The Club

3            Your Club

4            My Club

5            Some Club

6            Two Club

The idea is to link these tables based on the name of the club in the 'name' field of the second table AND have a row of output showing the person's name with each club he or she belongs to, like this:

l_name    f_name    belong_to

Smith     John      A Club

Jones     Mary      The Club

Jones     Mary      Your Club

Jones     Mary      My Club

Moore     Sue       The Club

Moore     Sue       A Club

Moore     Sue       Some Club

Wills     Art       My Club

Wills     Art       Some Club

Wills     Art       Two Club

The belong_to field in the first table can have multiple clubs separated by ', ' (i.e. comma space).

SELECT distinct id, l_name, f_name, trim(regexp_substr(belong_to, '[^,]+', 1)) club_str
FROM first_table

So far, this only returns the first club a person belongs to in each record of the first table.

1
  • 2
    If this is your design, I would recommend changing it. Storing comma separated lists in tables is a bad idea (for this very reason) Commented Feb 16, 2016 at 16:18

1 Answer 1

1

As Mark Sinkinson said storing comma separated list in the table is the bad idea.Reason there is a better structure to store list that is 'TABLE' :).

Instead you can create third table to store belonging club information. However you can get your result using the following query.

SELECT tbl1.id,
TRIM(REGEXP_SUBSTR(tbl1.belongto, '[^,]+',1, lines.column_value)) Belongto
FROM tbl1,
TABLE(CAST(MULTISET(SELECT level FROM dual CONNECT BY INSTR(tbl1.belongto,',',1, level-1)>0) AS SYS.odciNumberList))lines;

And look at the plan of the above query its quite expensive.:(

| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU) |Time     |

--------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   1 |  NESTED LOOPS               |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   2 |   TABLE ACCESS FULL         | TBL1 |     2 |    50 |     2   (0)| 00:00:01 |

|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|      |  8168 | 16336 |    32  (13)| 00:00:01 |

|*  4 |    CONNECT BY WITHOUT FILTERING     |      |       |       ||      |

|   5 |     FAST DUAL               |      |     1 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

So consider to change your design for betterment.
Thanks

1
  • Oh yes...I know it's a 'bad idea.' Someone has already stored the data that way. My job is resolving these square pegs wedged into round holes. I found your method...or one like it, much the same time you informed me of yours. Thank you for your help.
    – Uncle_Jake
    Commented Feb 18, 2016 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.