Re: Substitutes for subqueries (subselects)?

From: Oliver Stratmann <yxcsstratmoqwert_at_gmx.de>
Date: Thu, 4 Mar 2004 12:44:00 +0100
Message-ID: <c274q1$1q6k8g$1@ID-180535.news.uni-berlin.de>

Hello Kurta!

Are You probably looking for an outer join like:

SELECT sav_id, sav_memb, classtype

FROM  t_svgs svgs,
      t_cm_basebase cmbs,
      t_um_base umbs

WHERE svgs.sav_memb = 66470
and svgs.cmbs_id = cmbs.sav_cm_base (+)
and svgs.umbs_id = umbs.sav_um_base (+);

Bye!
Oli

PS: I'm sorry for posting this the wrong way first!

"Kurta" <submit_at_galleus.com> schrieb im Newsbeitrag news:efcb1994.0403040150.3a9a8284_at_posting.google.com...
> Thanks, Jan,
>
> But I meant to ask another thing... The question is not if there's a
> tool that converts my type 1 queries into type 2. I'd like to know (if
> someone can help me) if there's another way to achieve the desired
> output. That requires minos adjustments of the type 1 queries only. I
> read somewhere that there's always many ways to achieve the same thing
> in SQL :))
>
> Cheers,
>
> Kurta
>
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:<c24vad$1ogcsv$1_at_ID-93924.news.uni-berlin.de>...
> > "Kurta" <submit_at_galleus.com> schrieb im Newsbeitrag
> > news:efcb1994.0403020208.33ed38f_at_posting.google.com...
> > > I have a SELECT query that uses subselects:
> > >
> > > SELECT
> > > SAV_ID
> > > FROM
> > > T_SVGS
> > > WHERE
> > > (SAV_MEMB = 66470
> > > OR
> > > SAV_CM_BASE IN (SELECT CMBS_ID FROM T_CM_BASEBASE AA WHERE CMBS_MEMB
> > > = 66470)
> > > OR
> > > SAV_UM_BASE IN (SELECT UMBS_ID FROM T_UM_BASE BB WHERE UMBS_MEMB =
> > > 66470))
> > >
> > > I'd have to return additional columns from the tables of the
> > > subqueries. I can achieve a desired result with joins and unions like
> > > this:
> > >
> > > SELECT
> > > SAV_ID, SAV_MEMB, NULL CLASSTYPE
> > > FROM
> > > T_SVGS
> > > WHERE
> > > SAV_MEMB = 66470
> > >
> > > UNION
> > >
> > > SELECT
> > > SAV_ID, SAV_CM_BASE, CMBS.CLASSTYPE
> > > FROM
> > > T_SVGS, T_CM_BASEBASE CMBS
> > > WHERE CMBS_ID = SAV_CM_BASE AND CMBS_MEMB = 66470
> > >
> > > UNION
> > >
> > > SELECT
> > > SAV_ID, SAV_UM_BASE, UMBS.CLASSTYPE
> > > FROM
> > > T_SVGS, T_UM_BASE UMBS
> > > WHERE UMBS_ID = SAV_UM_BASE AND UMBS_MEMB = 66470
> > >
> > > Is there a less harmful way to get the same results without having to
> > > rewrite all the queries?
> > >
> > > Thanks for your help.
> > >
> > > Kurta
> >
> >
> > Kurta, I fear the answer is no, there is no nice tool for this.
> >
> > That would be a really nice tool, that could rewrite all concerning
selects
> > correctly
> > with *zero* knowledge of the business rules behind them
> > (and in the real world many lack to use fk constraints and so on ...)
> >
> > On the other hand, it could
> > actually make some SQL "artists" loosing their jobs ... maybe it will
come
> > with programming languages of the 10th generation in 2020, who knows ...
> >
> > So far, good SQL remains an art. Few are real artists, many arenīt.
> >
> > Not much help, sorry, but this is how I see it.
> > Greetings, Jan
Received on Thu Mar 04 2004 - 05:44:00 CST