I have recently just started using SQL and I am still getting used to the language. I am currently trying to add a left join to a script. The join is on an account reference which is the same in both tables, However the format is slightly different. Please see example below of tables and references I am trying to Join:
account_ref user_id
10000007 100000007
There is basically just an extra zero in the user_id column. How would I go about joining this I know you can just on partial matches for example the first 7 digits. I need the script to left join on the first 7 then miss the 8th and join on the 9th. If anyone could help me provide a line for this I would be grateful!
P.S this was what I was currently trying
LEFT JOIN adctwebpass j ON (e.account_ref 1,2,3,4,5,6,7) = (j.user_id 1,2,3,4,5,6,9)
I was expecting this to join the accounts and produce results. However it isn’t producing any results.
EGL2000 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
To handle a situation where you need to join tables on partially matching values with specific character positions, you’ll need to use string functions to extract and compare those parts of the fields. In SQL, you can use functions like SUBSTRING (or SUBSTR, depending on your SQL dialect) to extract parts of the strings for comparison.
Given your requirement to join on the first 7 digits of account_ref and the first 6 digits plus the 9th digit of user_id, you can achieve this using the LEFT and SUBSTRING functions. Here’s how you can write your SQL query:
SELECT e.*, j.*
FROM your_table1 e
LEFT JOIN adctwebpass j
ON LEFT(e.account_ref, 7) = LEFT(j.user_id, 6) || SUBSTRING(j.user_id FROM 9 FOR 1);
Explanation LEFT(e.account_ref, 7): Extracts the first 7 characters from account_ref. LEFT(j.user_id, 6): Extracts the first 6 characters from user_id. SUBSTRING(j.user_id FROM 9 FOR 1): Extracts the 9th character from user_id. LEFT(j.user_id, 6) || SUBSTRING(j.user_id FROM 9 FOR 1): Concatenates the first 6 characters and the 9th character of user_id to match the first 7 characters of account_ref.
For Different SQL Dialects PostgreSQL: Use SUBSTRING and || for concatenation. MySQL: Use SUBSTRING and CONCAT. SQL Server: Use SUBSTRING and + for concatenation. Let me know if you have any specific SQL dialect in mind or if any other details need addressing!
Shreyash Purwar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.