Left Join Question

From: <shorne_deja_com_at_my-deja.com>
Date: Wed, 18 Oct 2000 23:47:01 GMT
Message-ID: <8slcpk$pam$1@nnrp1.deja.com>

Thanks in advance for your help.

I am new to Oracle and writing Oracle SQL.

My problem is I have 2 tables that I need to combine. I need to separately restrict each table with a where clause and then do a left join between the 2 resultsets. I'm sure there is a way to do this in a single select statement, I just don't know how to do it. Thus the reason for this post.

Following is a very simplified version of my problem:

3 tables:

tblObject

---

ObjectID [PK]
ObjectDesc
ObjectType

tblRole

---

RoleID [PK]
RoleDesc

tblPermission

---

ObjectID [PK]
RoleID [PK]

tblPermission.ObjectID is FK to tblObject.ObjectID tblPermission.RoleID is FK to tblRole.RoleID

I need to do a left join between the following 2 resultsets:

select ObjectID, ObjectDesc
from tblObject
where ObjectType = 'A'

and

select tblPermission.ObjectID
from tblPermission
where tblPerission.RoleID = 'admin'

---

I tried the following and it works but isn't dynamic (that is, I need
to be able to change the where criteria):

create view vwTest1
as
select ObjectID, ObjectDesc
from tblObject
where ObjectType = 'A'

and

create view vwTest2
as
select tblPermission.ObjectID
from tblPermission
where tblPerission.RoleID = 'admin'

My final select statement went something like the following:

select vwTest1.ObjectID, vwTest1.ObjectDesc, vwTest2.ObjectID
from vwTest1, vwTest2
where vwTest1.ObjectID = vwTest2.ObjectID (+)

This produces the desired output:

ObjectID vwTest1.ObjectDesc vwTest2.ObjectID
-------- ------------------ ------------------
123      Object 123         123
234      Object 234         NULL
345      Object 345         NULL
456      Object 456         456
567      Object 567         NULL

[Note: I can then do a decode on the vwTest2.ObjectID value to convert
it to 'N' for NULL or 'Y' for not NULL.]

My interim solution has been to return the 2 resultsets to the client
application and have the client application combine the data. That is
more cumbersome than I like. I've considered encapsulating this "2
resultsets/left join" process within a stored procedure (using
temporary tables) or using a function to replace the tblPermission
piece, but a single select statement would be the best solution.

Again, thanks for your help.

Stephen Horne
shorne_at_ciscorp.com



Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Oct 18 2000 - 18:47:01 CDT