CODEWITHSUNDEEP
mysql
F Kempie
F Kempie

Reputation: 1

SQL: how do i find customer orders with customers?

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
;

Upvotes: 0

Views: 684

Answers (2)

MDWar
MDWar

Reputation: 113

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

Upvotes: 0

Frank
Frank

Reputation: 775

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

Upvotes: 5

Related Questions