[RESOLVED] How to group muliple records

[ade234uk]

I am trying to do a link up between 3 tables to extract some auction data.
I have got it work and it gives me the results that I need. However the custom_fields table contains muliple data for the same auction and is trippling up when I output the results.

The output I get is as follows

PURCHASE_DATE AUCTION_ID NAME BID AMOUNT CUSTOM
1236782702 46273 COI TEST 5 MR FITNESS
1236782702 46273 COI TEST 5 1.23
1236782702 46273 COI TEST 5 Brand New

What I need to do is be able to group the custom so I get the following output

PURCHASE_DATE AUCTION_ID NAME BID AMOUNT CUSTOM

1236782702 46273 COI TEST 5 MR FITNESS 1.23 Brand New

This one is stumping me. Sorry about the formatting above.

MySQL Query is below:

SELECT a.purchase_date, b.auction_id, b.name, a.bid_amount, c.box_value AS custom
FROM winners a
LEFT JOIN auctions b ON b.auction_id = a.auction_id
LEFT JOIN custom_fields c ON c.owner_id = a.auction_id
WHERE seller_id =13358

[djjjozsi]

GROUP_CONCAT is the answer.

i'm using this to create tags for news.

SELECT b.id, b.b , GROUP_CONCAT(a.name SEPARATOR ' ') AS tags FROM b LEFT JOIN a ON a.id=b.id GROUP BY b.id

b=news table
a=tags table

and in tags table the id field is the foreign key to the news table.

but in this context the custom ID would be lost.

another method is to make an IF condition when you print these results. (you need to see if the previous auction ID is different then the actual, if yes, you start building the next custom fields.)

[ade234uk]

Thank you very much, I shall try this tommorow.