Solved

JOIN tables on 1 of two Columns

Posted on 2011-09-19
4
321 Views
Last Modified: 2012-05-12
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
Comment
Question by:JustinW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 36562700
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
 
LVL 1

Author Comment

by:JustinW
ID: 36562824
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
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 36562880
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
 
LVL 1

Author Comment

by:JustinW
ID: 36562949
I was making it too hard;
extra parentheses threw an error.
Cleaned it up and worked like a charm!
Thanks!
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question