'SQL LEFT JOIN with WHERE and SUBSTR [duplicate]

I want to use SQL to left join two tables but only where the common column fits a substring criteria. How can this be accomplished?

CREATE TABLE MY_JOIN AS
    SELECT A.*, B.*
FROM DATA.TABLE_B AS B
LEFT JOIN DATA.TABLE_A AS A ON A.COMMON = B.COMMON
WHERE SUBSTR(A.COMMON,1,5) IN ('90210', '90211', '90212')

There are other questions that address using a left join with a where clause but do not specifically address using a substring function within the where clause.

sqlsasleft-joinwhere-clausesubstr


Solution 1:[1]

In the request comments you have clarified that you only want to select rows that match the given strings. In that case apply the SUBSTR function on b.common, not a.common:

CREATE TABLE my_join AS
SELECT a.*, b.*
FROM data.table_b AS b
LEFT JOIN data.table_a AS a ON a.common = b.common
WHERE SUBSTR(b.common, 1, 5) IN ('90210', '90211', '90212');

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Thorsten Kettner

Related Questions