How can I have a query with GROUP_CONCAT return more the one row?

Question

TXNID                PARENT_TXN_ID      ITEM_NAME

5G161631H9141883L                       Merchandise Total
4HD251984H4919123                       Woodworking 101@Fri 04-05-2013 8:00 pm
4LA48638GR031562M                       Woodworking 101@Fri 04-05-2013 8:00 pm
6TU64611FM249252D                       Woodworking 101@Fri 04-05-2013 8:00 pm
7S953179AV7967542                       Woodworking 101@Fri 04-05-2013 8:00 pm
04916720G2800071A                       Woodworking 101@Fri 04-05-2013 8:00 pm
test1               4HD251984H4919123   setet

the above is my data. here is my query.

SELECT ppv1.*,GROUP_CONCAT(ppv2.txn_id SEPARATOR ' <BR>') GC 
from paypal_valid ppv1
LEFT JOIN (SELECT txn_id,parent_txn_id from paypal_valid where parent_txn_id!='')
ppv2 ON ppv1.txn_id=ppv2.parent_txn_id
where ppv1.item_name!='Merchandise Total' and ppv1.parent_txn_id=''

This is only returning 1 row...I'm thinking it should return 5. I'm just not sure why there is only one row being returned.

you don't have a GROUP BY, so the GROUP_CONCAT just groups together every result row. — Eevee
– Eevee, Commented Mar 4, 2013 at 18:02

echo_Me · Accepted Answer · 2013-03-04 18:06:19Z

1

try this with group by

 SELECT ppv1.*,GROUP_CONCAT(ppv2.txn_id SEPARATOR ' <BR>') GC 
 from paypal_valid ppv1
LEFT JOIN (SELECT txn_id,parent_txn_id from paypal_valid where parent_txn_id!='')
 ppv2 ON ppv1.txn_id=ppv2.parent_txn_id
where ppv1.item_name!='Merchandise Total' and ppv1.parent_txn_id=''
group by ppv1.txn_id
                ^^^-------//make sure u have this in your ppv1 table

answered Mar 4, 2013 at 18:06

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I have a query with GROUP_CONCAT return more the one row?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related