0

I have two tables: 1. customer 2. customer_order

Customer table contains customer data(duh) and customer_order contains all the orders. I can join them on customer.id=customer_order.id_customer.

All fine, but now i want a query, where i have all the customer ids, and next the orders(customer_order.id) which these customers made (with order date)

like this:

customer 100    order 4, order 5, order 9
customer 101    order 7, order 8, order 15

I have this, but doesn't give me the result, it puts all the customer ids with an order on different rows:

SELECT c.id, c.firstname, co.id
FROM customer c
JOIN customer_order co
ON c.id=co.id_customer
;
Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
F Kempie
  • 1
  • Not group by - `order by c.id, c.firstname` – Nir Alfasi Aug 08 '13 at 15:51
  • How do you want the date to be displayed? Your example output doesn't show that. – Jim Aug 08 '13 at 15:52
  • possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) – Noel Aug 08 '13 at 15:53
  • Consider handling issues of data display at the application level/presentation layer if you have one (e.g. with a bit of PHP) – Strawberry Aug 08 '13 at 15:55

2 Answers2

5

You can use the group_concat function

select c.id, c.firstname, GROUP_CONCAT(co.id SEPARATOR ',')
from custom c
join custom_order co
group by c.id

this would return something like

customer 100 | 4,5,9
customer 101 | 7,8,15
Frank
  • 767
  • 5
  • 17
  • I got this result: Query : select c.id, c.firstname, GROUP_CONCAT(co.id SEPARATOR ',') from customer c join customer_order co group by c.id LIMIT 0, 100... Error Code : 126 Incorrect key file for table '/tmp/#sql_42a_0.MYI'; try to repair it Execution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000 – F Kempie Aug 13 '13 at 16:56
0

Have you tried:

SELECT c.id, c.firstname, co.id
FROM customer c
INNER JOIN customer_order co
ON c.id=co.id_customer
ORDER BY c.id;

It's either LEFT or INNER, you'll get different results depending on which you use, and I think for your purposes LEFT is the one you want to use. Then when you retrieve the data, you might have to drop it into: 

array["custid"][] = co.id
MDWar
  • 103
  • 13