Re: Tricky SQL

From: Randi Wølner <randiwolner_at_hotmail.com>
Date: Tue, 19 Jun 2001 13:04:05 +0200
Message-ID: <9gnbj9$ktl$1@oslo-nntp.eunet.no>

Here are some details, please excuse the table- and column names being in Norwegian (I did not design the model !):

I have one table "fremforing", where the column "kundeordre_kundeordreid" contains IDs that I will use as search criteria. "fremforing" is related to a table "LBforordre" in a one-to-many relationship.
In the column "fremforing_fremforingID" of table "LBforordre" one or more rows contains the value of column "fremforingID" in one row of "fremforing".

Then comes the linking to another table, called "TogElement". This is related to "LBforordre" in one of two ways:

If col "LBforordre.togelement_togelementID" contains a value, then that is one of the values found in "TogElement.togElementID".
If this column does not hold any value, then one more table called "Plassitransmid" must be used. Then the column "lastbarerforordre.lastbarerforordreID" holds the value of one the IDs in "Plassitransmid.LBfordre_Id". The col "Plassitransmid.togelement_togelementID" then keeps a reference to "Togelement.togElementID", so then this is the way to get to the row we want in table "Togelement".

This is only one part of the query, but I think it illustrates my problem: Could I make only ONE select-statement out of this, or do I have to have more, with UNION between them? How do I avoid joining with "Plassitransmid" for the rows of "fremforing" that is described in example 1 (is it possible?)

This would otherwise be the first query:

SELECT TOGELEMENT.TOGELEMENTID
FROM
  fremforing,
  LBForOrdre,
  Togelement
WHERE
   fremforing.KUNDEORDRE_KUNDEORDREID = 224    AND
   fremforing.FREMFORINGID = LBforordre.FREMFORING_FREMFORINGID    AND
   LBForOrdre.TOGELEMENT_TOGELEMENTID = Togelement.TOGELEMENTID

This is the second one:

SELECT TOGELEMENT.TOGELEMENTID
FROM

  fremforing,
  LBForOrdre,
  Togelement,

  Plassitransmid
WHERE
   fremforing.KUNDEORDRE_KUNDEORDREID = 224    AND
   fremforing.FREMFORINGID = LBforordre.FREMFORING_FREMFORINGID    AND
   LBForOrdre.TOGELEMENT_TOGELEMENTID IS NULL    AND
   LBForOrdre.lastbarerforordreID=Plassitransmid.Lbfordre_Id    AND
   Plassitransmid.Togelement_Togelementid = Togelement.Togelementid

Best regards,
Randi Wølner

Howard J. Rogers <howardjr_at_www.com> wrote in message news:3b2f1e4a_at_news.iprimus.com.au...
> No, I rather think the problem arises from (1) the lack of any *detail*
> about your problem. How about a few table/object definitions, and an idea
> of what relates to what, and an example of what you are trying to achieve?
>
> There is also (2): very few people (in my experience, at any rate) have
> actually made use of the object-relational features of Oracle 8.0 upwards,
> and so you are in a minority to start with (and the cynic in me tells me
we
> are about to find out why!)
>
> So, if you give us plenty of information, we might better be able to help.
> And many of us might be able to *learn* about object-relational techniques
> from the various answers that will undoubtedly be forthcoming when the
extra
> information is provided.
>
> Regards
> HJR
>
>
>
>
> "Randi Wølner" <randiwolner_at_hotmail.com> wrote in message
> news:9gmsui$hgj$1_at_oslo-nntp.eunet.no...
> > > My impression from what you wrote is that you haven't a clue about how
to
join
> > > tables. This may not be true but it is the impression you have given
to
this
> > > reader. Having "one starting table" makes no sense in an of itself. I
think you
> > > will need to post table structures and code examples for anyone to be
able
to
> > > help you.
> > >
> > > A query should contain only those tables necessary to create the
answer
set. If
> > > you are linking in extraneous tables either stop doing it or seriously
consider
> > > the fact that your schema is not properly designed.
> > >
> > > Daniel A. Morgan
> > >
> >
> > I know how to join tables when they are designed from a RDB point of
view.
> > My current question comes from trying to help a project that is working
> > "object oriented" - trying to store the object model into an Oracle
> > database. The content of the tables are not used as we as "database
people"
> > are used to see them, as columns can be used for more than one purpose -
> > f.ex. one column might hold a reference to the primary key in different
> > tables, as the class is allowed to reference objects of more than one
class.
> >
> > When I wrote that there is a table (class) where they "start" the
query -
I
> > meant that this is the table to which they want to compare the search
> > criterias. Then we have to join this table with 4-5 other tables (go
> > "through" them, to find the rows in the "result table" (yes, I know this
is
> > a bad term) - meaning table in which we find the columns that we're
> > searching for.
> >
> > The problem is that - according to the values of some of the columns,
there
> > are in some occations 4, in some 5 tables to be joined. From your answer
> > (and the lack of other response) it seems that this is not a very common
> > problem in the database world - maybe there is another newsgroup I shoud
> > rather try?
> >
> > Best regards,
> > Randi Wølner
> >
> >
>
>
Received on Tue Jun 19 2001 - 06:04:05 CDT