Solved

JOIN tables on 1 of two Columns

Posted on 2011-09-19
4
316 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
  • 2
  • 2
4 Comments
 
LVL 45

Expert Comment

by:Kdo
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:
Kdo 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 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