JustinW
asked on
JOIN tables on 1 of two Columns
I'm trying to figure out how to join 2 tables together, when I'm not sure
which field the join on the second table will be performed on.
I'm trying to join the following tables: AAD, ACCTS.
on ACCTS:ACCOUNT_NUMBER = (AAD:M_ACCT_REF_NUM or AAD:ACCT_NUM)
This doesn't work well at all;
Oracle won't even let me try this:
Any advices?
Thx
which field the join on the second table will be performed on.
I'm trying to join the following tables: AAD, ACCTS.
on ACCTS:ACCOUNT_NUMBER = (AAD:M_ACCT_REF_NUM or AAD:ACCT_NUM)
This doesn't work well at all;
LEFT OUTER JOIN ACCOUNTS ACCTS
ON (TRIM(leading '0' from AAD.M_ACCT_REF_NUM) || TRIM(leading '0' from AAD.ACCT_NUM))LIKE('%' || TRIM(leading '0' from ACCTS.ACCOUNT_NUMBER || '%'))
Oracle won't even let me try this:
LEFT OUTER JOIN ACCOUNTS ACCTS
ON (TRIM(leading '0' from AAD.M_ACCT_REF_NUM) = (TRIM(leading '0' from ACCTS.ACCOUNT_NUMBER))
OR TRIM(leading '0' from AAD.ACCT_NUM)) = (TRIM(leading '0' from ACCTS.ACCOUNT_NUMBER))
Any advices?
Thx
ASKER
I should clarify,
I know the col in t1 that I want to use,
but not the col in t2.
thanks!
I know the col in t1 that I want to use,
but not the col in t2.
SELECT
t1.USER_NAME,
t2.SOMETHING
FROM t1
LEFT OUTER JOIN t2
ON t1.col_1 = t2.col_1 OR t2.col_2
thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was making it too hard;
extra parentheses threw an error.
Cleaned it up and worked like a charm!
Thanks!
extra parentheses threw an error.
Cleaned it up and worked like a charm!
Thanks!
You're making it too tough on yourself. :)
SELECT *
FROM table1 t1
LEFT OUTER JOIN table t2
ON t1.col1 = t2.col1
AND (t1.col2 = t2.col2 OR t1.col2 = t2.col3)
or, if you want col3 when col2 is null:
SELECT *
FROM table1 t1
LEFT OUTER JOIN table t2
ON t1.col1 = t2.col1
AND t1.col2 = coalesce (t2.col2, t2.col3)
or, if you have known condition where you want col2 or col3
SELECT *
FROM table1 t1
LEFT OUTER JOIN table t2
ON t1.col1 = t2.col1
AND t1.col2 = case when {condition} t2.col2 else t2.col3 end
Good Luck,
Kent