Multiple Column Outer Join

[dannymm]

Hi,

I have a problem with multiple column outer join. Here is my schema

Table: parent

module : Integer
name : varchar
id : Integer
desc : Text

Table: child

module : Integer
id1 : Integer
id2 : Integer
id3 : Integer
status : Char(1)

Here id1,id2,id3 points to parent.id

Here i want to fetch all those records from "parent" and "child" where "id1" or "id2" or "id3" is equal to "id" from parent.

This should be outer join i,e even if there are no matches in "child", it should fetch the "parent" records with child columns null.

my current query for a single column join is (I am using Oracle)

select parent.*,child.* from parent,child where parent.module = 'my module' and child.module (+) = 'my module' and parent.id = child.id1 (+)

This works fine for one column ..i,e id1 .. my problem is "How do i modify the above query to work for id1,id2 and id3"

Thanks in Advance ..

Regards,
Danny ..

 

[SantaMufasa]

Danny,

As you probably already discovered, Oracle doesn't let you do an outer join using "OR", so what you must do is use the UNION set operator, producing the following code:

select parent.*,child.*
from parent,child where parent.module = child.module (+)
and parent.id = child.id1 (+)
union
select parent.*,child.*
from parent,child where parent.module = child.module (+)
and parent.id = child.id2(+)
union
select parent.*,child.*
from parent,child where parent.module = child.module (+)
and parent.id = child.id3(+)

Let me know how this works for you,

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:05 (28Nov03) GMT, 01:05 (28Nov03) Mountain Time)

 

[SantaMufasa]

Danny,

Some other issues you need to address:

1) You cannot have a column named "DESC" (Parent table).
2) You cannot compare an integer column (Module) to a character string, 'my module'.
3) Oracle doesn't support data type, 'text' (Parent.descr).

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:12 (28Nov03) GMT, 01:12 (28Nov03) Mountain Time)

 

[dannymm]

Thanks SantaMufasa, the union works fine for me.

This was just a dummy table structure (since i can't post the actual table strucure here), anyways thanks for highlighting

Regards,
Danny !

 

[dannymm]

Hi Mufasa,

I have encountered one more problem here. There are some rows in child which do not have any relation with parent i,e id1,id2 and id3 are blank. How can i modify the above query so that it will fetch all those child rows which do not have any relation with parent.

Regards,
Danny !

 

[SantaMufasa]

Danny,

Good question...Just add yet another UNION to the batch:

select parent.*,child.*
from parent,child where parent.module = child.module (+)
and parent.id = child.id1 (+)
union
select parent.*,child.*
from parent,child where parent.module = child.module (+)
and parent.id = child.id2(+)
union
select parent.*,child.*
from parent,child where parent.module = child.module (+)
and parent.id = child.id3(+)
union
select null,null,null,null,child.*
from child where id1||id2||id3 is null;

How does that sound?

Mufasa
(aka Dave of Sandy, Utah, USA @ 07:44 (01Dec03) GMT, 00:44 (01Dec03) Mountain Time)

 

[dannymm]

Thanks a lot dave. The query works fine now.

Danny !