Reputation: 1
I'm not sure how to write a join clause that takes a value from table 1, then searches a string in table 2 to see if they match. Sound confusing?
Here's the actual example I'm working with.
Table 1
Customer_Id Concat_Phone_Numbers
1 8888888888;1111111111
Table 2
Caller Callee Calldate
1111111111 3333333333 1/1/1900
I want to create a table that looks like this:
Desired Table
Customer_Id Calldate
1 1/1/1900
I'm lost when it comes to writing the join clause so that the entire list in Table 1's second column is searched for a matching phone number/entry.
Thank you in advance for your help! (PS it's my first time asking a question!)
Edit::
Here's where I'm at now
Select
*
from table1
left join table2
on ??????????????????
Upvotes: 0
Views: 28
Reputation: 1271241
Yuck! You should fix the data structure. You really need a table with one row per customer and per phone number. You'll understand why if you care about performance.
But, if you are stuck with this data model, you can do a join using string and/or array operations. Here is a method using regular expressions
select . . .
from table1 t1 left join
table2 t2
on t2.caller ~ '^' || replace(t1.phone_numbers, ';', '|') || '$' or
t2.callee ~ '^' || replace(t1.phone_numbers, ';', '|') || '$' ;
Upvotes: 1