SQL join in one row in a query

Question

I have two tables:

products(id1, productname)
commands(id2,#id1,username)

I want to show for example: for a particular username alex all the products(productname) that he bought in this format

  productname           |username
  dssd,dsds,sds         |Alex

I don't like to show all the products that he bought in multiple rows ! I want them just in one row. Please Can any one help. How we can do this with SQL?

Thanks in advance :)

Don't substitute your field names for fluff; use the real ones, maybe explain a little bit about your structure. Understanding the "business domain" of code helps us understand the code better. `x`, `a`, `id2` etc don't mean anything whereas `user_id`, `product_id`, `booking_date` etc mean things — Joe, Sep 12 '14 at 10:14
See [this](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) and [this](http://stackoverflow.com/questions/9788670/select-multiple-rows-in-one-result-row) — Rohith, Sep 12 '14 at 10:38

score 2 · Accepted Answer · answered Sep 12 '14 at 10:40

Using GROUP_CONCAT:-

SELECT a.username, GROUP_CONCAT(b.productname)
FROM commands a
INNER JOIN products b
ON a.id1 = b.id1
GROUP BY a.username

You can change the separator if required, eliminate duplicates and change the order of the concatenated items easily.

SQL join in one row in a query

1 Answers1