Solved

Oracle Conditional Join?

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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

919 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

12 Experts available now in Live!

Get 1:1 Help Now