subquery in informix with mutliple rows

zephyr_hex (Megan)
zephyr_hex (Megan) used Ask the Experts™
in sql 2005, i can use the following subquery in the SELECT clause of my sql query:
,(select table2.usr_id + ',' from table2 where table2.table_id = table1.table_id for xml path('')) as techname

if there are multiple usr_id 's in table2, they are returned as one field, with commas between each value.

what is the Informix equivalent?

p.s.  multiset seems to be reserved for db owner, and i don't have owner permissions.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi. What does SQL2005 do with the multiple usr_id's in the subquery when it matches against the value in the main query?

In other words is your query saying ...

where maintable,usr_id matches any of the table2.usr_id's returned by the subquery?

in which case you can use
WHERE maintable.usr_id IN (SELECT table2.usr_id WHERE table2.table_id = table1.table_id)

(There are other relationships such as ALL - used to test if a valus is greater or less than all the values returned etc etc)

Senior Developer
As this is imho a SQL Server speciality, you have to use a concatenation function of your own, e.g.

--> stefan <--
Top Expert 2010


flutophilus:  sql server 2005 returns the following:

value1, value2, value3

(it returns all matches as a string delimited by commas)

ste5an - i'll take a look at the concat

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial