subquery in informix with mutliple rows
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.
,(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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
value1, value2, value3
(it returns all matches as a string delimited by commas)
ste5an - i'll take a look at the concat
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)
HTH