Link to home
Start Free TrialLog in
Avatar of JustinW
JustinWFlag for United States of America

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;
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 || '%'))

Open in new window


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))

Open in new window


Any advices?
Thx
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi Justin,

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
Avatar of JustinW

ASKER

I should clarify,

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

Open in new window


thanks!
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JustinW

ASKER

I was making it too hard;
extra parentheses threw an error.
Cleaned it up and worked like a charm!
Thanks!