Oracle Conditional Join?

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?
yelbowAsked:
Who is Participating?
 
AkenathonConnect With a Mentor Commented:
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
 
yelbowAuthor Commented:
That seems perfect - thanks!

Well deciphered from my ramblings too ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.