Re: Left Join Question

hi stephen,

did you try this:

select
  o.ObjectID, o.ObjectDesc, p.ObjectID
from
  tblObject o, tblPermission p
where
  o.ObjectID = p.ObjectID(+)
  AND o.ObjectType = 'A' and p.RoleID = 'admin'

i didn't try this one on my instance but am pretty sure that it should work to produce desired results.

HTH, ATTA In article <8slcpk$pam$1_at_nnrp1.deja.com>,   shorne_deja_com_at_my-deja.com wrote:
> 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.
>

--

getting the meanin' of data...


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