user2595861 user2595861 - 2 months ago 9
MySQL Question

Condition in GROUP_CONCAT selection

I have 3 tables and I'm joining them to get some data.


-----------------
Table Name: users
-------------------------------
|user_id | user_name |
-------------------------------
123 | abc
-------------------------------
223 | bcd
-------------------------------
323 | cde
-------------------------------

-----------------
Table Name: limit
-------------------------------
user_id | limit_id
-------------------------------
123 | 1
-------------------------------
223 | 2
-------------------------------
323 | 3
-------------------------------
323 | 4
-------------------------------

-------------------------
Table Name: limit_setting
-------------------------------
limit_id | date_limit
-------------------------------
1 | 2016-09-29 12:00:00
-------------------------------
2 | 2016-09-28 12:00:00
-------------------------------
3 | 2016-09-27 12:00:00
-------------------------------
1 | 2016-09-27 12:00:00
-------------------------------
1 | 2016-09-24 12:00:00
-------------------------------
4 | 2016-09-25 12:00:00
-------------------------------
4 | 2016-09-26 12:00:00
-------------------------------


I need to get a result like this. I am stuck with the GROUP_CONCAT for the dates column.
The date column should have all entries other than the MAX date. If there is only one entry in the
limit_setting
table for that limit_id then it shouldn't show anything for that user.
count_dates
: its the number of entries which are there in the
limit_setting
table.



Desired output

----------------------------------------------------------------------
user_name | dates | count_dates
----------------------------------------------------------------------
abc | 2016-09-27 12:00:00 , 2016-09-24 12:00:00 | 3
----------------------------------------------------------------------
bcd | | 1
----------------------------------------------------------------------
cde | | 1
-----------------------------------------------------------------------
cde | 2016-09-26 12:00:00 | 2
-----------------------------------------------------------------------


SELECT PP.`user_name`, count(ESL.Limit_id) as count_dates,
GROUP_CONCAT(ESL.date_limit SEPARATOR ',') as dates
FROM users as PP INNER JOIN `limit` as PAL ON PP.Id = PAL.PlayerId
LEFT JOIN limit_setting as ESL ON ESL.LimitId = PAL.limitId
GROUP BY PAL.limitId


Additionally i tried with (which returned nothing)

SELECT ESL.date_limit, MAX(date_limit) as max_date, PP.`user_name`, count(ESL.Limit_id) as count_dates,
GROUP_CONCAT(ESL.date_limit SEPARATOR ',') as dates
FROM users as PP INNER JOIN `limit` as PAL ON PP.Id = PAL.PlayerId
LEFT JOIN limit_setting as ESL ON ESL.LimitId = PAL.limitId
GROUP BY PAL.limitId
HAVING ESL.date_limit > max_date


I tried with
Find_in_set
but not sure how to use it effectively.

Answer

Try this:

SELECT user_name,        
       CASE WHEN COUNT(*) > 1 
            THEN SUBSTRING_INDEX(GROUP_CONCAT(date_limit ORDER BY date_limit), 
                                 ',', COUNT(*) - 1) 
            ELSE ''
       END AS dates,             
       COUNT(*) AS count_dates
FROM users as PP 
INNER JOIN `limit` as PAL ON PP.user_id = PAL.user_id
LEFT JOIN limit_setting as ESL ON ESL.limit_id = PAL.limit_id
GROUP BY user_name

The query uses SUBSTRING_INDEX function in order to get all dates returned by GROUP_CONCAT except from the last date. Using ORDER BY inside GROUP_CONCAT we can place the maximum date at the end, so that SUBSTRING_INDEX truncates exactly this date.

Demo here

Comments