Re: SQL question

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.