?
Solved

Oracle Conditional Join?

Posted on 2011-03-14
2
Medium Priority
?
700 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
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.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

719 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