How to join to tables with wildcards?

Question

I have table1, and table2 as shown below. I would like to run a left join to merge column C from table2 into table1 based on columnA & columnB. The caveat is that table2 containing wildcard character (*). How should I modify the left join statement to produce the result table

SELECT * 
FROM table1
LEFT JOIN table2 ON table1.a = table2.a
LEFT JOIN table2 ON table1.b = table2.b 
                AND ????

Answer 1

You don't really have an outer (left) join as you only want the table1 results.

Given you want * result to be included this is part of the join criteria.

select t1.a, t1.b, t2.c
from table1 as t1
join table2 as t2
on (t1.a = t2.a and t2.b = '*') 
   or (t2.a = '*' and t1.b = t2.b)

This is based on the expect results. There are edge cases like both having * or both having non-* that aren't included in your test data.

Answer 2

Thanks danblack for your recommendation. Unfortunately, it didn't quite lead to the desired result. Here is short script that I wrote to test out your recommendation:

import pandas as pd 
import sqlite3
import numpy as np
cnx = sqlite3.connect(':memory:')
cursorObj = cnx.cursor()

tb1 = pd.DataFrame([[1,1,2,2,2,3,3,3],[1,2,1,2,3,1,2,3]],index=['a','b'],dtype=str).T
tb2= pd.DataFrame([[2,np.NaN,np.NaN,np.NaN],[np.NaN,1,2,3],['x', 'y', 'm','n']],index=['a','b','c'],dtype=str).T
tb2.fillna('*', inplace=True) 

tb1.to_sql('tb1',cnx,index=False,if_exists='replace')
tb2.to_sql('tb2',cnx,index=False,if_exists='replace')

sqlcmd = '''
SELECT TB1.A, TB1.B, TB2.C,TB2.A 
FROM TB1
JOIN TB2 ON (TB1.A = TB2.A AND TB2.B ='*')
OR  (TB2.A ='*' AND TB1.B = TB2.B)
'''
pd.read_sql_query(sqlcmd, cnx)

@danblack This is what works for me

sqlcmd = '''
SELECT TB1.A, TB1.B, COALESCE(TB2.C, TB3.C) as C
FROM TB1
LEFT JOIN TB2 TB3 ON (TB1.B = TB3.B AND TB3.A IS NULL)
LEFT JOIN TB2 ON (TB1.A = TB2.A AND TB2.B IS NULL)
'''
pd.read_sql_query(sqlcmd, cnx)