Re: Left Join Question

I talked with an Oracle dba who recommended the following:

select a.ObjectID, a.ObjectDesc, b.ObjectID from
(select ObjectID, ObjectDesc from tblObject where ObjectType = 'A') a, (select ObjectID from tblPermission where RoleID = 'admin') b, where a.ObjectID = b.ObjectID(+)

In essence, this is the same as the "2 views then outer (left) join" option described below. This works and returns the required records (including the nulls from the right table). A simpler select statement would be preferred but the above solution works. If anyone has a better solution please let me know.

Thanks,
Stephen Horne
shorne_at_ciscorp.com

In article <8slrsf$53g$1_at_nnrp1.deja.com>,   Atta ur-Rehman <atta707_at_my-deja.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 19 2000 - 10:34:13 CDT