Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Left Join Question

Re: Left Join Question

From: <shorne_deja_com_at_my-deja.com>
Date: Thu, 19 Oct 2000 15:13:25 GMT
Message-ID: <8sn32f$3kn$1@nnrp1.deja.com>

Atta,

Thank you for your help.

I should have indicated in my initial posting that I had tried essentially what you recommended, but could not get it to work correctly. I may have been doing it wrong, but it appears to do the join first then the where clauses. Going back to my sample results, your recommendation (again, unless I am doing it wrong) yields something like the following:

o.ObjectID o.ObjectDesc p.ObjectID
---------- ------------ ----------

123        Object 123   123
456        Object 456   456

The records with a null p.ObjectID were eliminated. I need the null records in the final result:

o.ObjectID o.ObjectDesc p.ObjectID
---------- ------------ ----------

123        Object 123   123
234        Object 234   NULL
345        Object 345   NULL
456        Object 456   456
567        Object 567   NULL

If you or anyone else has any additional ideas to share I would definitely appreciate it.

Again, thanks for your help!
Stephen

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:13:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US