Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Left Outer Join not giving me desired results

Status
Not open for further replies.

dbrs4me

MIS
May 26, 2010
24
US
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.

Code:
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:

Code:
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
 
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

Code:
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
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top