Solved

Oracle Conditional Join?

Posted on 2011-03-14
2
694 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
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

759 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

21 Experts available now in Live!

Get 1:1 Help Now