[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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
0
JustinW
Asked:
JustinW
  • 2
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
JustinWAuthor Commented:
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!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Yep.  All three of the examples above do that.  In those examples they always join on col1 to show that you can mix "fixed" join items and "variable" join items.  They work just as well if you don't use col1 as part of the join key.

  SELECT *
  FROM table1 t1
  LEFT OUTER JOIN table t2
    ON (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.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.col2 = case when {condition} t2.col2 else t2.col3 end


But the DBMS has no idea why you need to join the rows.  You need to decide why you'd join on one column or the other, then code to it.

Why would you join on the M_ACCT_REF_NUM column and why would you join on the ACCT_NUM column?


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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now