Solved

JOIN tables on 1 of two Columns

Posted on 2011-09-19
4
314 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I was making it too hard;
extra parentheses threw an error.
Cleaned it up and worked like a charm!
Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now