Left Outer Join not giving me desired results

[dbrs4me]

Hello,

I am trying to create a view, but to do that you need good SQL code. I am looking at two tables, I have represented them below. The current set of code works, but not the way I need it to. See the code and the Actual vs. Desired results.

Select Main.id, Main.Sub_ID, SM1.parent_id, SM2.parent_id, SM3.parent_id
From Main LEFT OUTER JOIN SubMain SM1 on (Main.Sub_ID = SM1.id)
	  LEFT OUTER JOIN SubMain SM2 on (SM1.parent_id = SM2.id)
	  LEFT OUTER JOIN SubMain SM3 on (SM2.parent_id = SM3.id)

Table Main

ID Sub_ID
----------------
1000 48
1001 36
1002 24


Table SubMain

ID Parent_ID
-------------------
48 36
36 24
24 12
12 null



Acutal Result

Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 36 24 12
1001 36 24 12 null
1002 24 12 null null


Desired Result

Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 12 24 36
1001 36 12 24 null
1002 24 12 null null


Is there a change I can make to the join or select statement that will enable me to supress null values? I have tried to change the select statement to:

Select Main.id, Main.Sub_ID, SM3.parent_id, SM2.parent_id, SM1.parent_id

The output changes, but not how I hoped:

Results

Main. Main. SubMain. SubMain. Submain.
ID Sub_ID Parent_ID1 Parent_ID2 Parent_ID3
-------------------------------------------------------------
1000 48 12 24 36
1001 36 null 12 24
1002 24 null null 12

The second SELECT statement actually gets me closer to what I want because the nulls are first, but I haven't been able to get the data to line up the way I need it to.

Any help is greatly appreciated.

Thanks - Charlie

 

[Thargy]

dbrs,

to assist myself and others, I have produced the following script which I believe builds the tables and populates them with your data. Please run it, and confirm whether or not it's correct

DROP TABLE MAIN CASCADE CONSTRAINTS PURGE;
DROP TABLE SUBMAIN CASCADE CONSTRAINTS PURGE;

CREATE TABLE MAIN
(
ID     INTEGER,
SUB_ID INTEGER
);

ALTER TABLE MAIN ADD CONSTRAINT PK_MAIN PRIMARY KEY(ID);
ALTER TABLE MAIN ADD CONSTRAINT UQ_MAIN__SUB_ID UNIQUE (SUB_ID);


CREATE TABLE SUBMAIN
(
ID        INTEGER,
PARENT_ID INTEGER
);

--ALTER TABLE SUBMAIN ADD CONSTRAINT FK_SUBMAIN__MAIN
--FOREIGN KEY (PARENT_ID) REFERENCES MAIN(SUB_ID);

INSERT INTO MAIN (ID, SUB_ID) VALUES (1000,48);
INSERT INTO MAIN (ID, SUB_ID) VALUES (1001,36);
INSERT INTO MAIN (ID, SUB_ID) VALUES (1002,24);

INSERT INTO SUBMAIN(ID,PARENT_ID) VALUES (48,36);
INSERT INTO SUBMAIN(ID,PARENT_ID) VALUES (36,24);
INSERT INTO SUBMAIN(ID,PARENT_ID) VALUES (24,12);
INSERT INTO SUBMAIN(ID,PARENT_ID) VALUES (12,NULL);

COMMIT;

Regards

T

 

[Dagon]

The obvious way to suppress the NULL values would be to change the outer joins to inner joins, but I daresay that's not what you want. If there is no match on a row between say Main and SM2, what do you want to display for SM2.id?

For Oracle-related work, contact me through Linked-In.

 

[dbrs4me]

Thargy, I was able to create the tables using your script. It responds the way I stated above.

Dagon, There is always at least one match between main and submain so this isn't an issue. I think if it were missing from submain I would just get a null value.