how to group_concat using distinct correctly - MYSQL

Question

I have 3 table relation using MYSQL;

Example first as riders table:

bib   |   series_id    | point
 202         3             200
 219         3             140
 202         2             200
 219         2             110
 10          1             90

Example second as series table:

series_id   |   series_no  |   season_id
     1            1                2
     2            2                1
     3            1                1

Example third as seasons table:

 season_id   |   year
     1            2015
     2            2016

How to GROUP_CONCAT point correctly? I'm trying like this

SELECT riders.bib, seasons.year, GROUP_CONCAT(DISTINCT riders.point ORDER BY series.series_no DESC) AS seriPoint 
    FROM series, riders, seasons 
    GROUP BY riders.bib

I'm getting output seriPoint for bib: 202 is 200 and bib: 219 is 140,110 when I'm using DISTINCT output like that. But when I'm not using DISTINCT getting output seriPoint for bib: 202 is 200,200,200,200 and bib: 219 is 140,110,140,110. What I want is output seriPoint for bib: 202 is 200,200 and bib: 219 is 140,110.

ADD: please help to add filter too, for season_id when different season_id its to be different row.

Answer 1

yes you are getting correct output since you have used DISTINCT. BTW, you should change your query to use proper JOINS

SELECT riders.bib, 
seasons.year, 
GROUP_CONCAT(DISTINCT riders.point ORDER BY series.series_no DESC) AS seriPoint 
FROM riders
JOIN series ON series.series_id = riders.series_id
JOIN seasons ON series.season_id = seasons.season_id
GROUP BY riders.bib;

(OR) you can get the grouping first and then perform join like

select seasons.year, xx.bib, xx.seriPoint
FROM series
JOIN (  
select series_id, bib 
group_concat(point) as seriPoint 
from riders
group by bib ) xx ON series.series_id = xx.series_id
JOIN seasons ON series.season_id = seasons.season_id
order by xx.seriPoint;