Use JOIN or something else?

[dtyson2000]

Hello...

 

The following query:

SELECT customers.*, orders.*, orders_products.* FROM customers, orders, orders_products WHERE (customers.customers_id = orders.customers_id) && (orders.orders_id = orders_products.orders_id);

returns orders placed by customers which are entered into the database as separate rows for each product ordered. So If Customer 1 orders a dog, a cat and a bird, it returns three rows saying such (each with the same customer info, same order id but different product):

 

customers_id | orders_id | orders_products

1 | 1 | dog

1 | 1 | cat

1 | 1 | bird

 

I would like to return this information in one row saying:

 

customers_id | orders_id | orders_products

1 | 1 | dog, cat, bird

 

As it is, when I use GROUP BY either customers_id or orders_id, I get only the last row in the set (i.e. 1 | 1 | bird).

 

I've tried various JOINS but am not sure how this is to be done.

 

Anybody able to help out?

[effigy]

See this topic.

[worldworld]

Use GROUP BY in sql statement as a solution... 

[fenway]

Yup, GROUP_CONCAT() is the way to go, if you must do it in SQL.