AW: SQL question

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