Left join select last and first row and Left join select some row as column

Question

First of all I show my current query.

SELECT u.id, u.username, GROUP_CONCAT(DISTINCT up.weight) as weights, GROUP_CONCAT(DISTINCT pd.type_change, ':', pd.sum_type_change) as statistics
FROM user u
LEFT JOIN
    (SELECT up.weight, up.user_id FROM user_parameter up WHERE up.date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00' ORDER BY up.user_id, up.date ASC) up
    ON u.id = up.user_id
LEFT JOIN
    (SELECT pd.type_change, count(pd.id) as sum_type_change, dd.user_id FROM diet_day dd
     LEFT JOIN product_day pd ON dd.id = pd.diet_day_id
     WHERE dd.date_start BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00' GROUP BY dd.user_id, pd.type_change) pd
    ON u.id = pd.user_id
GROUP BY u.id

This works fine but I have to substract first and last up.weight instead of

GROUP_CONCAT(DISTINCT up.weight)

In addition to this I have to change

GROUP_CONCAT(DISTINCT pd.type_change, ':', pd.sum_type_change)

This always return string like this [1: count, 2: count, 3: count, 4: count] Instead of one column (group_concat) I need to have four new columns.

It is possible to do all of this in mysql query?

You can't return a variable number of columns in regular SQL. — Barmar, Nov 17 '16 at 20:09

score 1 · Accepted Answer · edited May 23 '17 at 11:59

You'll need separate subqueries to get the first and last up.weight.

SELECT u.id, u.username, lastup.weight - firstup.weight as delta_weight, 
       GROUP_CONCAT(DISTINCT pd.type_change, ':', pd.sum_type_change) as statistics
FROM user u
LEFT JOIN
    (SELECT up.weight, up.user_id 
     FROM user_parameter up
     JOIN (SELECT user_id, MAX(up.date) AS maxdate
           FROM user_parameter
           WHERE date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00'
           GROUP BY user_id) AS maxup
     ON up.user_id = maxup.user_id AND up.date = maxup.maxdate
     WHERE up.date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00') lastup
    ON u.id = lastup.user_id
LEFT JOIN
    (SELECT up.weight, up.user_id 
     FROM user_parameter up
     JOIN (SELECT user_id, MIN(up.date) AS mindate
           FROM user_parameter
           WHERE date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00'
           GROUP BY user_id) AS minup
     ON up.user_id = maxup.user_id AND up.date = minup.mindate
     WHERE up.date BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00') firstup
    ON u.id = firstup.user_id
LEFT JOIN
    (SELECT pd.type_change, count(pd.id) as sum_type_change, dd.user_id 
     FROM diet_day dd
     LEFT JOIN product_day pd ON dd.id = pd.diet_day_id
     WHERE dd.date_start BETWEEN '2015-10-10 00:00:00' AND '2017-10-10 00:00:00' GROUP BY dd.user_id, pd.type_change) pd
    ON u.id = pd.user_id
GROUP BY u.id

Regarding getting the statistics in separate columns instead of concatenating them with GROUP_CONCAT, see MySQL pivot table

Thank you for trying to help me. First left join returns 0 rows. I think that happens because JOIN inside this query get one record with MAX date in all table, not MAX per user — Karpol, Nov 17 '16 at 20:38
You're right, I forgot `GROUP BY user_id` in the inner subqueries. — Barmar, Nov 17 '16 at 20:54

Left join select last and first row and Left join select some row as column

1 Answers1