List:General Discussion« Previous MessageNext Message »
From:Bob Ramsey Date:July 17 2004 11:52am
Subject:different kind of nested selects
View as plain text  
Let's say I have two tables:

T1:

Name
-------
apple
banana
cherry


T2:

value| name
-----|------
1      | apple
2      | banana
3      | banana
4      | cherry
5      | apple
6      | apple

I want to get a result that looks like this:

name| all_values
apple| 1,5, 6
banana| 2,3
cherry| 4

In my head, the select statement looks like this:

select t1.name, (select t2.value from t2 where t2.name=t1.name) as 
all_values from t1;

Is there a way to do this with just one sql statement?

Thanks,

bob
Thread
stress testBrad Waddell17 Jul
  • different kind of nested selectsBob Ramsey17 Jul
  • Re: stress testEric Bergen17 Jul
  • Re: stress testEgor Egorov19 Jul