Solved

Oracle Conditional Join?

Posted on 2011-03-14
2
699 Views
Last Modified: 2012-05-11
I have two tables:

  CREATE TABLE table1
   (	table1_key	CHAR(5),
        LEVEL1	    VARCHAR2(20),
		LEVEL2	    VARCHAR2(20),
		LEVEL3	    VARCHAR2(20),
		LEVEL4	    VARCHAR2(20),
		LEVEL5	    VARCHAR2(20),
   );
   
   CREATE TABLE table2
   (	table2_key	CHAR(5),
        LEVEL1	    VARCHAR2(20),
		LEVEL2	    VARCHAR2(20),
		LEVEL3	    VARCHAR2(20),
		LEVEL4	    VARCHAR2(20),
		LEVEL5	    VARCHAR2(20),
   );
   
   INSERT INTO table1 
   VALUES ('00001', 'A', null, 'C', null, null);
   
   INSERT INTO table1 
   VALUES ('00002', '1', '2', '3', '4', null);

   INSERT INTO table2 
   VALUES ('00001', 'A', 'B', 'C', null, null);
   
   INSERT INTO table2 
   VALUES ('00002', '1', '2', '3', null, null);

Open in new window

 
For the record that has a key of "00001", the data in the two tables is almost identical, except that LEVEL2 in table2 contains a 'B', and in table1 it's a null.

I want to write a query that, for every column that has value other than null in table1 check that against table2.  So, for key "00001" in table1 it would find a match as there is a record in table2 which has a key of "00001", a LEVEL1 of "A" and a LEVEL3 of "C".  So, for key 00001 the join is pretty much:

	table1.table1_key = table2.table2_key 
	table1.LEVEL1     = table2.LEVEL1
	table1.LEVEL3     = table2.LEVEL3

Open in new window


For the record that has a key of "00002", the data in the two tables is almost identical, except that LEVEL4 in table1 contains a '4', and in table2 it's a null.

In this instance no match should be found on table2 as LEVEL4 on table1 has content, and on table2 it is null.  

	table1.table1_key = table2.table2_key 
	table1.LEVEL1     = table2.LEVEL1
	table1.LEVEL2     = table2.LEVEL2
	table1.LEVEL3     = table2.LEVEL3
	table1.LEVEL4     = table2.LEVEL4

Open in new window


How can a build a join on all 6 levels that is conditional and only works if the field in table1 has content other than null?
0
Comment
Question by:yelbow
[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 Comments
 
LVL 11

Accepted Solution

by:
Akenathon earned 500 total points
ID: 35131067
I found the examples hard to follow, so I'll concentrate on this:

>> I want to write a query that, for every column that has value other than null in table1 check that against table2

Try this:

table1.table1_key = table2.table2_key AND
(table1.LEVEL1     = table2.LEVEL1 OR table1.LEVEL1 IS NULL) AND
(table1.LEVEL2     = table2.LEVEL2 OR table1.LEVEL2 IS NULL) AND
(table1.LEVEL3     = table2.LEVEL3 OR table1.LEVEL3 IS NULL) AND
(table1.LEVEL4     = table2.LEVEL4 OR table1.LEVEL4 IS NULL) AND
(table1.LEVEL5     = table2.LEVEL5 OR table1.LEVEL5 IS NULL) AND
(table1.LEVEL6     = table2.LEVEL6 OR table1.LEVEL6 IS NULL)

Open in new window

0
 

Author Closing Comment

by:yelbow
ID: 35131253
That seems perfect - thanks!

Well deciphered from my ramblings too ;)
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

617 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