Re: SQL question

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

I'd guess that that is how the old engine worked - the new one has a lot 
more options with its various nodes

Charles Wilt wrote:
> Birgitta,
>
> I was under the impressing that the optimizer would rewrite the query
> with the sub-select in the in predicate as a join  if at all possible.
>
> Is that not the case?
>
> Charles
>
>
> On Wed, Oct 22, 2008 at 2:11 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:
>   
> > Hi,
> >
> > yet two other ways to solve your problem.
> >
> > 1. Solution with a Sub-Select in the Where-Clause
> > Select a.*
> >  from (Select * from MyTable
> >           where UserId = 'UserId1') as x
> >       join MyTable a using(Assets)
> >
> > 2. Solution with a Common Table Expression
> > with x as (Select * from Mytable
> >             where UserId = 'UserId1')
> > Select a.*
> > from x join Mytable a using (Assets)
> >
> > Because of performance issues, both solutions should be preferred over the
> > solution with the sub-selects in the in predicate.
> >
> > Mit freundlichen Grüßen / Best regards
> >
> > Birgitta Hauser
> >
> > "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> > Brown)
> > "If you think education is expensive, try ignorance." (Derek Bok)
> > "What is worse than training your staff and losing them? Not training them
> > and keeping them!"
> >
> > -----Ursprüngliche Nachricht-----
> > Von: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Tim Gornall
> > Gesendet: Wednesday, 22. October 2008 16:41
> > An: midrange-l@xxxxxxxxxxxx
> > Betreff: SQL question
> >
> >
> > I ended up doing this in two parts, and was wondering how/if it could be
> > done in one statement.  The table contains two columns: UserID and Asset.
> >
> > I need to determine all the assets associated with UserID = user1.  Then
> > using that result set, all the users associated with those assets.
> >
> > The end result would look something like this:
> > User1  asset1
> > User1  asset2
> > User1  asset3
> > User5  asset2
> > User8  asset1
> > User8  asset3
> >
> > Anyone want to take a stab at it?
> > Thanks, Tim
> >
> >
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> >
> >