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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Nested joins and ODBC

Re: Nested joins and ODBC

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Mon, 08 Apr 2002 14:44:34 GMT
Message-ID: <m1is8.189409$ZR2.101238@rwcrnsc52.ops.asp.att.net>


Sure, but thinking you had so much experience I assumed you would already know.

Conceptually, the first sql is a join (inner or equal join) so you get all the rows that match and the second is the set of records that don't match. Unioning them together gets you the set of rows that are joined and the set that isn't. If you still can't figure it out try doing a little research. Maybe later I'll post a simple example, but I have to go to work now. Jim
"Silvio Bierman" <sbierman_at_idfix.nl> wrote in message news:a8s7tv$9o$1_at_news1.xs4all.nl...
> Jim,
>
> Would you please explain to me how a union could replace a join? Unions
are
> used to merge compatible rows resulting from subqueries into a single
> resultset. Joins are used to merge columns from different (related) tables
> into the rows of a resultset. They correspond to complementary operations
> from relational algebra. Neither of them can be replaced by the other.
>
> Silvio
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:IFgs8.236449$702.41206_at_sccrnsc02...
> > Actually, why don't you use unions? Even more database neutral than the
> > inner join syntax. If you have been developing since Oracle 6 then I
> think
> > that would be the first thing to try.
> > Jim
> > "Silvio Bierman" <sbierman_at_idfix.nl> wrote in message
> > news:a8ror8$lcp$1_at_news1.xs4all.nl...
> > > Daniel,
> > >
> > > Thanks for responding to my post.
> > >
> > > I would like to set the record straight: I have been developing Unix
> based
> > > Oracle applications (mainly server apps for voice-response
environments,
> > > some C/S desktop apps) since Oracle 6 and have years of experience as
> both
> > > application developer and database admin. I have a thorough
> understanding
> > > about the fundamental differences between Oracle and SQL-Server.
> > Therefore,
> > > I am not expecting Oracle to mimic SQL-Server behaviour.
> > >
> > > However, if Oracle distributes an ODBC-driver it automatically takes
on
> > the
> > > burden of conforming to some of the mainstream behaviour most
> ODBC-enabled
> > > database platforms display. One particularly important area is that of
> > > adhering to as much as possible of the ODBC SQL-grammar for SELECT,
> INSERT
> > > and UPDATE statements. If I am to develop a multi-backend app using
ODBC
> > > proprietary join operators simply aren't an option, be they Oracles
(+)
> or
> > > SQL-Servers (*). The only way to overcome these differences is to rely
> on
> > > the ability of the ODBC-driver to interpret the ODBC join syntax and
> > convert
> > > to the proper backend-syntax.
> > >
> > > Therefore, my question is not so much about the Oracle database server
> as
> > it
> > > is about the Oracle ODBC driver. If it is not able to do nested
> ODBC-style
> > > joins I think it is useless, despite the undisputed excellence of the
> > > backend!
> > >
> > > Silvio Bierman
> > >
> > > "damorgan" <damorgan_at_exesolutions.com> wrote in message
> > > news:3CAE3CFD.BEA87090_at_exesolutions.com...
> > > > No you don't. What you need to do is get an Oracle book, open it,
and
> > read
> > > > it.
> > > >
> > > > I work in Redmond, WA. Many of my friends work for Microsoft. But I
> have
> > > to
> > > > confess I am getting sick and tired of the MS mentality which is
that
> > > > whatever way I did it in a Microsoft product is the way it works and
> > > anything
> > > > else is the work of a medieval demon or worse. Get over it. Quickly!
> > > >
> > > > If you are going to work in Oracle the first thing you need to learn
> is
> > > that
> > > > it is not a Microsoft product. It does not work like a Microsoft
> > product.
> > > It
> > > > is not supposed to work like a Microsoft product. And if Larry
doesn't
> > > lose
> > > > his mind it never will. The syntax is different, the architecture is
> > > > different, transactions are different, data integrity is different,
> > > locking
> > > > is different, and the very basis of the system is multiversioning
> which
> > > SQL
> > > > Server doesn't have.
> > > >
> > > > So unless you have some over-riding desire to make a huge ugly mess
> and
> > > then
> > > > blame it on Oracle get a book and read it. The problems with Oracle
> will
> > > not
> > > > be the software. If you make an insecure, unscalable, lousy
performing
> > > home
> > > > to corrupt data put the blame squarely where it belongs ... on
> yourself.
> > > >
> > > > Back to your question: Can it be done? Of course. But only by people
> who
> > > > learn how!
> > > >
> > > > Daniel Morgan
> > > >
> > > >
> > > >
> > > > Silvio Bierman wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I am porting a SQLServer app to Oracle 8 (using Oracle
ODBC-driver)
> > and
> > > I
> > > > > have stumbled onto a problem.
> > > > >
> > > > > I need to join three tables together using the join syntax:
> > > > >
> > > > > select * from a left outer join b on a.x=b.y left outer join c on
> > > b.i=c.j
> > > > >
> > > > > When I join only two tables I can use
> > > > >
> > > > > select * from {oj a left outer join b on a.x=b.y}
> > > > >
> > > > > but this does not work when joining more than two tables.
> > > > >
> > > > > Can this be done?
> > > > >
> > > > > Because the app needs to run on SQLServer also I can not use the
> > Oracle
> > > > > outer join syntax!
> > > > >
> > > > > Regards,
> > > > >
> > > > > Silvio Bierman
> > > >
> > >
> > >
> >
> >
>
>
Received on Mon Apr 08 2002 - 09:44:34 CDT

Original text of this message

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