CODEWITHSUNDEEP
sqlstringpostgresqljoin
groundfalll
groundfalll

Reputation: 1

PostgreSQL: Joining Tables Based on Searched Concatenated Strings

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions