I'm trying to figure out how to model a user-managed lookup table to map types of calls to other types of web visits. Basically I want to associate a call to customer service about contact info change to web pages related to contact info change. My initial thought is something like this.
This is SQL Server if that helps - Azure Synapse serverless, but syntax is less important at the moment than concept.
| id | call_topic | web_url |
|---|---|---|
| 1 | Contact Info | site.com/contactinfo |
Using this I can find calls by the same person using both calls.member_id = web_traffic.member_id AND (some join to my lookup table).
To further extend this model, what if I use basic "like" or maybe regex, with a lookup table like this? (using "like")
| id | call_topic | web_url |
|---|---|---|
| 1 | Contact% | site.com/contact% |
You might read this as "any call topic that starts with 'Contact' and any web_url that starts with 'site.com/contact'". But I don't know what this join looks like.
If you want to use the "like" operator or regular expressions to perform pattern matching for joining your lookup table, you can proceed with LIKE to match the call topic and web URL patterns defined in the lookup table:
SELECT *
FROM calls
LEFT JOIN web_traffic ON calls.member_id = web_traffic.member_id
LEFT JOIN lookup_table ON calls.call_topic LIKE lookup_table.call_topic
AND web_traffic.web_url LIKE lookup_table.web_url
If you want to use regular expressions for more complex pattern matching, you can make use of the LIKE operator with the ESCAPE clause and define your regular expression patterns:
SELECT *
FROM calls
LEFT JOIN web_traffic ON calls.member_id = web_traffic.member_id
LEFT JOIN lookup_table ON calls.call_topic LIKE lookup_table.call_topic ESCAPE '!'
AND web_traffic.web_url LIKE lookup_table.web_url ESCAPE '!'