Home » SQL & PL/SQL » SQL & PL/SQL » Outher Join Problem
Outher Join Problem [message #255971] Thu, 02 August 2007 10:34 Go to next message
Reber
Messages: 5
Registered: August 2005
Location: Turkey
Junior Member

Hello,

I have four table and simplified table versions can be created with below script...

CREATE TABLE Test_A(AId NUMBER(1), AName Varchar2(20));
CREATE TABLE Test_B(BId NUMBER(1), AId NUMBER(1), CId NUMBER(1), BName Varchar2(20));
CREATE TABLE Test_C(CId NUMBER(1), DId NUMBER(1), CName Varchar2(20));
CREATE TABLE Test_D(DId NUMBER(1), DName Varchar2(20));

INSERT INTO Test_A VALUES (1, 'A1');
INSERT INTO Test_A VALUES (2, 'A2');
INSERT INTO Test_A VALUES (3, 'A3');

INSERT INTO Test_B VALUES (1, 1, 1, 'B1');
INSERT INTO Test_B VALUES (2, 1, 3, 'B2');
INSERT INTO Test_B VALUES (3, 2, 3, 'B3');



INSERT INTO Test_C VALUES (1, 1, 'C1');
INSERT INTO Test_C VALUES (2, 2, 'C2');
INSERT INTO Test_C VALUES (3, 2, 'C3');

INSERT INTO Test_D VALUES (1, 'D1');
INSERT INTO Test_D VALUES (2, 'D2');

Here key point is I wanna get a Test_A item even it doesn't have a relation with Test_B (In case of AId = 3). Also I wanna get only the Items which Dname is 'D1', so I write below queries but it doesn't work Sad

SELECT * FROM TEst_A A, TEst_B B, TEST_C C, TEST_D D
where A.AID = 3 AND B.AID (+) = A.AID AND C.Cid (+) = B.Cid AND D.did (+) = C.Did
and D.DName (+) = 'D1'

SELECT * FROM TEst_A A, TEst_B B, TEST_C C, TEST_D D
where A.AID = 1 AND B.AID (+) = A.AID AND C.Cid (+) = B.Cid AND D.did (+) = C.Did
and D.DName (+) = 'D1'

I supposed both query bring 1 row first one worked but latter not Sad Where is my fallacy?

FIRST QUERY WORKS, I got what I want.

LATTER NOT Sad
WHAT I GOT:

1 1 A1 1 1 1 B1 1 1 C1 1 D1
2 1 A1 2 1 3 B2 3 2 C3 NULL NULL

WHAT I WANT:
1 1 A1 1 1 1 B1 1 1 C1 1 D1

Atilla ACAR

[Updated on: Thu, 02 August 2007 10:39]

Report message to a moderator

Re: Outher Join Problem [message #255998 is a reply to message #255971] Thu, 02 August 2007 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then you can't have the result you said.
You have a condition a.aid=1 and your result displays one line with a.aid=2.

Regards
Michel

Re: Outher Join Problem [message #256127 is a reply to message #255998] Fri, 03 August 2007 01:57 Go to previous messageGo to next message
Reber
Messages: 5
Registered: August 2005
Location: Turkey
Junior Member

Thanks Michel Smile... Sry I am not a carefull reader Smile)
Here my scripts:

CREATE TABLE Test_A (
  AId   NUMBER(1),
  AName VARCHAR2(20));

CREATE TABLE Test_B (
  BId   NUMBER(1),
  AId   NUMBER(1),
  CId   NUMBER(1),
  BName VARCHAR2(20));

CREATE TABLE Test_C (
  CId   NUMBER(1),
  DId   NUMBER(1),
  CName VARCHAR2(20));

CREATE TABLE Test_D (
  DId   NUMBER(1),
  DName VARCHAR2(20));

INSERT INTO Test_A
VALUES     (1,
            'A1');

INSERT INTO Test_A
VALUES     (2,
            'A2');

INSERT INTO Test_A
VALUES     (3,
            'A3');

INSERT INTO Test_B
VALUES     (1,
            1,
            1,
            'B1');

INSERT INTO Test_B
VALUES     (2,
            1,
            3,
            'B2');

INSERT INTO Test_B
VALUES     (3,
            2,
            3,
            'B3');

INSERT INTO Test_C
VALUES     (1,
            1,
            'C1');

INSERT INTO Test_C
VALUES     (2,
            2,
            'C2');

INSERT INTO Test_C
VALUES     (3,
            2,
            'C3');

INSERT INTO Test_D
VALUES     (1,
            'D1');

INSERT INTO Test_D
VALUES     (2,
            'D2');


Here key point is I wanna get a Test_A item even it doesn't have a relation with Test_B (In case of AId = 3). Also I wanna get only the Items which Dname is 'D1', so I write below queries but it doesn't work for A.AID = 1 ...

SELECT *
FROM   TEst_A A,
       TEst_B B,
       TEST_C C,
       TEST_D D
WHERE  A.AID = 3
       AND B.Aid (+)  = A.Aid
       AND C.Cid (+)  = B.Cid
       AND D.did (+)  = C.Did
       AND D.DName (+)  = 'D1'


That works without any problem. And returns, (In previous I forgot to exculude rownumbers from pl/sql developer)

3 A3 NULL NULL NULL NULL NULL NULL NULL NULL NULL

SELECT *
FROM   TEst_A A,
       TEst_B B,
       TEST_C C,
       TEST_D D
WHERE  A.AID = 1
       AND B.Aid (+)  = A.Aid
       AND C.Cid (+)  = B.Cid
       AND D.did (+)  = C.Did
       AND D.DName (+)  = 'D1'


That does not work, and returns;

1 A1 1 1 1 B1 1 1 C1 1 D1
1 A1 2 1 3 B2 3 2 C3 NULL NULL

I expected,

1 A1 1 1 1 B1 1 1 C1 1 D1

Regards,

Atilla ACAR
Re: Outher Join Problem [message #256132 is a reply to message #256127] Fri, 03 August 2007 02:03 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
First attempt (untested!):
SELECT v.aname
     , d.dname
FROM   ( SELECT a.aname
              , b.aid
              , NVL(b.cid,-1) cid
         FROM   Test_A a
            ,   Test_B b
         WHERE  a.aid = b.aid(+)
       ) v
    ,  Test_C c
    ,  Test_D d
WHERE  d.did = c.did
AND    d.dname = 'D1'
AND    NVL2(v.aid, c.cid, v.cid) = v.cid
/


MHE
Re: Outher Join Problem [message #256179 is a reply to message #256132] Fri, 03 August 2007 05:29 Go to previous messageGo to next message
Reber
Messages: 5
Registered: August 2005
Location: Turkey
Junior Member

Thanks but It have some problem Sad
I got,
A3 D1
A1 D1

I should get,
A3 NULL
A1 D1

[Updated on: Fri, 03 August 2007 05:31]

Report message to a moderator

Re: Outher Join Problem [message #256196 is a reply to message #256179] Fri, 03 August 2007 06:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I think this is what you are after

SELECT a.aid, nvl(d.dname, 'NULL')
FROM   TEst_A A,
       TEst_B B,
       TEST_C C,
       TEST_D D
WHERE  
       B.Aid (+)  = A.Aid
       AND C.Cid (+)  = B.Cid
       AND D.did (+)  = C.Did
       AND 
       (	
	D.DName  = 'D1'
        or
	d.dname is null
        )


Output is

       AID NVL(D.DNAME,'NULL')
---------- --------------------
         1 D1
         3 NULL
Re: Outher Join Problem [message #256229 is a reply to message #256196] Fri, 03 August 2007 08:26 Go to previous message
Reber
Messages: 5
Registered: August 2005
Location: Turkey
Junior Member

Thanks it is really what I want Smile

Are there any common way to solve queries that have many outer joins? How oracle process such queries? For example, Can we say when we use outer join with a constant shall we use NVL instead of Outer join?

D.DName  = 'D1'
        or
	d.dname is null
instead of 

D.DName (+) = 'D1'



Regards,
Atilla ACAR
Previous Topic: Selecting details from the 3 most recent rows
Next Topic: Exclusive Select By Historical Data
Goto Forum:
  


Current Time: Sat Dec 10 04:51:08 CST 2016

Total time taken to generate the page: 0.07224 seconds