By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,474 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,474 IT Pros & Developers. It's quick & easy.

two nested select statements

P: n/a
JS
Let's say I have two tables:

t1
myname
apple
banana
cherry

t2
myname | value
apple | 1
banana | 2
apple | 3
banana | 4
cherry | 5
I want to do one query and get back the data like this:

+--------+-----------+
| myname | all_value |
+--------+-----------+
| apple | 1,3 |
| banana | 2,5 |
| cherry | 4 |
+--------+-----------+
In my head, it seems like the query should look like:

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

But that just doesn't work. Is this possible without resorting to
code?

Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
jd***@hotmail.com (JS) wrote in
news:b0**************************@posting.google.c om:
Let's say I have two tables:

t1
myname
apple
banana
cherry

t2
myname | value
apple | 1
banana | 2
apple | 3
banana | 4
cherry | 5
I want to do one query and get back the data like this:

+--------+-----------+
| myname | all_value |
+--------+-----------+
| apple | 1,3 |
| banana | 2,5 |
| cherry | 4 |
+--------+-----------+
In my head, it seems like the query should look like:

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

But that just doesn't work. Is this possible without
resorting to code?

Thanks


Moving each value to a comma delimited list will require code. If
you can live with

+--------+-----------+
| myname | all_value |
+--------+-----------+
| apple | 1 |
| apple | 3 |
| banana | 2 |
| banana | 5 |
| cherry | 4 |
| lemon | 0 |

try
SELECT t1.name,nz(t2.value,0) AS all_value
FROM t1 LEFT JOIN t2 ON t1.name=t2.name
ORDER BY t1.name,t2.value.

If the query is to feed a report, you can set up the detail section
to print multiple columns, giving the appearance you want.

If you really want it in the query as a comma delimited list,
you'll need a function that creates the list. Note to MicroSoft:
there really should be a domain aggregate function that does that.

Bob Quintal
Nov 13 '05 #2

P: n/a
This will do it:
http://www.mvps.org/access/modules/mdl0004.htm
"Return a concatenated list of sub-record values"
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.