CODEWITHSUNDEEP
mysqlsqlgroup-concat
amburnside
amburnside

Reputation: 1903

MySQL GROUP CONCAT duplicates

I have a table of results that looks like this:

score_id, uid, af_id, level, record_date

Where uid is the user id of a user on the system. af_id is a focus which a level has been set at. An af_id will be either 1.1, 1.2, 1.3. Level is an integer value, ranging from 3 - 7.

A sample data set would be:

1, 2, 1.1, 4, 2012-09-02
2, 2, 1.2, 4, 2012-09-02
3, 2, 1.3, 3, 2012-09-02
4, 2, 1.3, 4, 2012-11-30

I need to query the database to collect what level a user has reached for each af_id between certain dates. I am using GROUP_CONCAT to bring the data into one row. My query works for the first period of time, but produces duplicates there after. My SQL so far is:

SELECT u.uid, u.forename, u.surname, ui.gender, GROUP_CONCAT(us.level ORDER BY us.af_id SEPARATOR ',') AS levels
FROM users u
LEFT OUTER JOIN user_scores us
ON us.uid=u.uid AND us.record_date > '2012-09-01' AND us.record_date < '2012-10-20'
JOIN user_info ui ON ui.uid =u.uid
GROUP BY u.uid
ORDER BY u.surname ASC

This gives the result:

Joe Bloggs  M  4,4,3

I want to be able to widen the months so that my query will be able to return updated results for af_id's. So if I widen the results dates to range between 2012-09-01 and 2012-12-31. I would like to see the following results:

Joe Bloggs  M  4,4,4

However, my query (using the above SQL) returns the following result:

Joe Bloggs  M  4,4,3,4

Any help with this would be much appreciated. Thanks in advance.

Upvotes: 1

Views: 499

Answers (2)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

Revised query. You state that it should be able to retrieve historic, and sounds like you are just interested in the highest level attained. To this end the inner query just needs to limit the max date on user_scores, and can simply retrieve uid, af_id, and the MAX(level).

This can then be joined onto users u, and in turn GROUP_CONCAT can return you the focuses and their relevant max scores. If an additional focus is added later then it will be returned.

This will not return focus 1.2 if a user has no scores in focus 1.2 

SELECT 
    u.uid, 
    u.forename, 
    u.surname, 
    ui.gender,
    GROUP_CONCAT(us.af_id) AS focusOrder,
    GROUP_CONCAT(us.level ORDER BY us.af_id ASC) AS focusMaxLevels
FROM users u

INNER JOIN user_info ui 
    ON ui.uid =u.uid

INNER JOIN (
    SELECT
        uid,
        af_id,
        MAX(level) as level
    FROM user_scores
    WHERE record_date <= DATE('2012-10-20')
    GROUP BY uid, af_id
    ORDER BY uid ASC, af_id ASC 
) AS us
ON us.uid = u.uid
GROUP BY u.uid
ORDER BY u.surname ASC

Upvotes: 0

piotrm
piotrm

Reputation: 12356

It seems you get your results for each af_id by pure coincidence - there is exactly one row matching your set date range for each af_id in your example data. You need to group by uid,af_id first to get level, I'm guessing here you need the highest one, so MAX() would by suitable.

SELECT u.uid, u.forename, u.surname, ui.gender, GROUP_CONCAT(us.level ORDER BY us.af_id ) AS levels
FROM users u
LEFT OUTER JOIN
( SELECT  uid, af_id, MAX(level) as level
  FROM user_scores 
  WHERE record_date > '2012-09-01' AND record_date < '2012-12-31'
  GROUP BY uid, af_id ) us
ON us.uid=u.uid
JOIN user_info ui ON ui.uid =u.uid
GROUP BY u.uid
ORDER BY u.surname ASC

Upvotes: 2

Related Questions