0

Ok, so I have three tables I'm working on combining to create my pivot table. For simplicity, I'll only reference the fields I'm using for this.

employees->userTblID, firstName, lastName

lms__trainings->trainingID, trainingName

lms__complete->completeID, trainingID, employeeID(userTblID)

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(b.trainingID = ''',F.trainingID, ''', b.completionDate, NULL)) AS ''', F.trainingName,"'")) INTO @sql FROM lms__trainings AS F INNER JOIN lms__complete AS G ON F.trainingID=G.trainingID;
SET @sql = CONCAT('SELECT a.userTblID, a.firstName, a.lastName, ', @sql, ' FROM employees AS a LEFT JOIN lms__complete AS b ON a.userTblID=b.employeeID WHERE a.cloudID=1 GROUP BY a.userTblID ORDER BY a.lastName');
PREPARE stmt FROM @sql;
EXECUTE stmt;

It generates the PIVOT table fine, but it only includes trainingName columns that have an associated record in lms__complete. If I remove the inner join from line 2 or change it to a left join (which in my thought would not limit it to lms__complete entries and give you all the trainingNames in lms__trainings) it gives the good old 1064 error.

I know it's just not sending all trainingIDs through the procedure. I'm thinking my LEFT JOIN on line 3 should be a LEFT JOIN of a UNION between lms__trainings and lms__complete, but I haven't put the correct search parameters in google for the last couple days to find what I need.

CWentzloff
  • 43
  • 5
  • If you get an error, add the error message. It can help tremendously in identifying your problem. For the most likely suspect based on your description look [here](https://stackoverflow.com/q/17791505/6248528). – Solarflare Jun 06 '18 at 17:01
  • THAT'S IT!!! Wasn't aware of the limit, but I up'd it to SET SESSION group_concat_max_len = 5000; and changed the second line to a LEFT JOIN like I wanted originally, it it works perfectly. – CWentzloff Jun 06 '18 at 18:49

1 Answers1

0

Solorflare really gave the answer here. The trick was to get over the default value of group_concat_max_len.

I did this by setting

SET SESSION group_concat_max_len = 5000;

So my stored procedure now looks like

SET @sql = NULL;
SET SESSION group_concat_max_len = 5000;

SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(b.trainingID = ''',F.trainingID, ''', b.completionDate, NULL)) AS ''', F.trainingName,"'")) INTO @sql FROM lms__trainings AS F LEFT JOIN lms__complete AS G ON F.trainingID=G.trainingID;

SET @sql = CONCAT('SELECT a.userTblID, a.firstName, a.lastName, ', @sql, ' FROM employees AS a LEFT JOIN lms__complete AS b ON a.userTblID=b.employeeID WHERE a.cloudID=1 GROUP BY a.userTblID ORDER BY a.lastName');

PREPARE stmt FROM @sql;

EXECUTE stmt;
CWentzloff
  • 43
  • 5