Sub-Query Returns More than one row

Question

I have a subquery in my SELECT statement that returns more than one row, but I need both values. Is there anyway to concatenate the multiple values with a comma to make the outside query think the subquery is only returning one value?

Example of my query:

select o.id,
   (select v.value v from values v join attributes a on v.att_id=a.att_id where a.att_id='100' and v.id=o.id)
from objects o
where o.class_id='GGzX';

Thanks in advance!

thanks Don, trying to figure out if that post will work with my subquery — Brandon Lehman, Jul 10 '13 at 21:11

Gaston Flores · Accepted Answer · 2013-07-10T21:47:05.347

3

Try with this query, maybe will be useful (click here to test the query):

CREATE TABLE TEST1(
ID INT);

CREATE TABLE TEST2(
    ID INT,
    TXT VARCHAR2(100));

INSERT INTO TEST1 VALUES(1);
INSERT INTO TEST1 VALUES(2);
INSERT INTO TEST1 VALUES(3);

INSERT INTO TEST2 VALUES(1,'A');
INSERT INTO TEST2 VALUES(1,'B');
INSERT INTO TEST2 VALUES(2,'C');
INSERT INTO TEST2 VALUES(3,'A');
INSERT INTO TEST2 VALUES(3,'B');
INSERT INTO TEST2 VALUES(3,'C');

/* HERE IS THE QUERY!!!*/
SELECT A.ID, 
(SELECT listagg(B.TXT,',' ) WITHIN GROUP (ORDER BY B.ID)
FROM TEST2 B WHERE B.ID = A.ID
) AS CONTATENATED_FIELD
FROM TEST1 A;

NOTE: listagg works in 11.X versions, please see this link for more information.

According with your query maybe you need something like this:

select o.id,
(SELECT listagg(v.value,',' ) WITHIN GROUP (ORDER BY v.value) from values v join attributes a on v.att_id=a.att_id where a.att_id='100' and v.id=o.id)
from objects o
where o.class_id='GGzX';

edited Jul 10 '13 at 21:47

answered Jul 10 '13 at 21:11

Gaston Flores

2,457
3
23
42

This is what I'm trying per Don's suggestion thru an alternate post, however my "B.txt" is actually a sub query. Can you use LISTAGG with a subquery? – Brandon Lehman Jul 10 '13 at 21:17
Nevermind I see what you did there...looks like it works initially... – Brandon Lehman Jul 10 '13 at 21:23
Anyway, i would recommend you try to get out this subquery. – Gaston Flores Jul 10 '13 at 21:29
Your example in the 2nd code block worked perfectly...thanks – Brandon Lehman Jul 10 '13 at 21:43
You're welcome, do not forget, if possible avoid complex subqueries, in this case, maybe you can extract the joins in the subquery, and put outside it. Thanks. – Gaston Flores Jul 10 '13 at 21:52

M Khalid Junaid · Answer 2 · 2013-07-10T21:13:36.423

0

Just add the GROUP BY a.att_id or limit your query

select o.id,
   (select v.value val from values v join attributes a on v.att_id=a.att_id
 where a.att_id='100' and v.id=o.id GROUP BY a.att_id)
from objects o
where o.class_id='GGzX';

OR


select o.id,
   (select v.value val from values v join attributes a on v.att_id=a.att_id
 where a.att_id='100' and v.id=o.id LIMIT 1)
from objects o
where o.class_id='GGzX';

OR just group the values by any seperator

select o.id,
   (select group_concat( v.value SEPARATOR ' / ') val from values v join attributes a on v.att_id=a.att_id
 where a.att_id='100' and v.id=o.id GROUP BY a.att_id)
from objects o
where o.class_id='GGzX';

edited Jul 10 '13 at 21:13

answered Jul 10 '13 at 21:07

M Khalid Junaid

63,861
10
90
118

actually my goal is to get the multiple values into one row, kind of as if my sub query is returning one value – Brandon Lehman Jul 10 '13 at 21:10
See my updated answer `group_concat` query will do this job – M Khalid Junaid Jul 10 '13 at 21:11

score 0 · Answer 3 · answered Jul 10 '13 at 21:23

0

try COALESCE

http://msdn.microsoft.com/en-us/library/ms190349.aspx

example found here ... http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d7a8ccf0-1a40-4068-b6b4-dc568c1cc165/select-query-with-multiple-row-coalesce-subquery

answered Jul 10 '13 at 21:23

RoMEoMusTDiE

4,739
1
17
26

Sub-Query Returns More than one row

3 Answers3