Data in my tables
item
item_id name
1 ball
2 cap
3 bat
order
order_id date time
1 06/01/2013 15:12
2 12/01/2013 11:10
3 06/01/2013 23:10
4 23/01/2013 10:55
orderform
order_id item_id quantity
1 1 2
1 3 4
2 1 1
3 2 3
3 1 2
3 3 1
4 1 2
At the moment I'm using following sql query:
SELECT
order_id,
group_concat(txt) AS txt,
date,
time
FROM (
SELECT
orderform.order_id AS order_id,
order.date AS date,
order.time AS time,
CONCAT(orderform.quantity, ' x ',item.name) AS txt
FROM order orderform
LEFT JOIN item ON orderform.item_id = item.item_id
) AS baseview
GROUP BY order_id
And I get this, date and time are not synchronized to id.
order_id name date time
1 2 x ball,4 x bat 06/01/2013 15:12
2 ball 06/01/2013 23:10
3 3 x cap,2 x ball,bat 06/01/2013 15:12
4 2 x ball 06/01/2013 15:12
It should look like this. Shall I do another sub select within this query just for time and date?. Thanks for help.
order_id name date time
1 2 x ball,4 x bat 06/01/2013 15:12
2 ball 12/01/2013 11:10
3 3 x cap,2 x ball,bat 06/01/2013 23:10
4 2 x ball 23/01/2013 10:55
Thanks for help.
Actually, you can get your desired result even without using a subquery.
SELECT a.order_ID,
GROUP_CONCAT(CONCAT(b.quantity, ' x ',c.name) SEPARATOR ', ') ItemName,
MAX(a.date) date,
MAX(a.time) time
FROM `order` a
INNER JOIN orderform b
ON a.order_id = b.order_id
INNER JOIN item c
ON b.item_id = c.item_id
GROUP BY a.order_ID