TABLE_A
CARGO - HELPERS 250 - 125|126|5|...(AS MANY AS THEY ARE ASSIGNED ON THE PROGRAM)
TABLE_B
ID - NAME 5 - JAKE 125 - JOSH 126 - MEGAN 9001 - VEGETA 10 - SPANISH INQUISITION
How can I join table A and B to get all those who helped in the cargo?
Will I need to write a function?
I did not develop the program. I’m creating a dashboard and need that info.
Using ORACLE 11g DB
CONCLUSION — SOLVED
After a bit of research and help of friends I was able to get this SQL that worked in my case.
SELECT CARGO, HELPERS FROM TABLE_A INNER JOIN table_b B ON B.ID IN (SELECT regexp_substr(HELPERS,'[^|]+', 1, level) FROM dual CONNECT BY regexp_substr(HELPERS,'[^|]+', 1, level) IS NOT NULL)
The use of regex to get all until the first |
, then continue until can’t find another pipe. Not the correct explanation but can help you.
Advertisement
Answer
In a sense, this is easy:
... from table_a a join table_b b on '|' || a.helpers || '|' like '%|' || b.id || '|%' ...
But, as I said in a Comment under your question, the better approach would be to fix your data model (if it’s within your power). Your TABLE_A is not in First Normal Form. No wonder you had difficulty writing this (trivial) join; you will feel much more pain for less trivial tasks, not to mention slow execution, no ability to check the validity of “helpers”, etc.