i know this is kind of question that might have been asked before.
But i am my self not sure if i am doing it the right way so i have to ask.
i want this below table in output
EmpID Name Desig Year Q1 Q2 Q3 Annual --------------------------------------------------------------------------------- 1 Haider 0 2016 setup setup setup AnotherString 2 Arif 1 2016 setup setup setup AnotherString
But the tables i have in db are.
Employee Table:
EmpID Name Desig ------------------------------- 1 Haider 0 2 Arif 1
Appraisal Table
ID Title ----------------- 1 Q1 2 Q2 3 Q3 4 Annual
The values in appraisal table are the columns in the output table(first table).
I don't understand how can i do that with pivot. i have seen examples but not sure how to have table setup like this.
Plus There is no year information in the table. the year will be displayed from the current year against every employee.
This is kinda query i tried to work on. but i don't know how to join it to other table there is no employee information there or anything.
SELECT
*
FROM
employee E
INNER JOIN employment ET
ON ET.`employee_id` = E.`employee_id`
AND ET.`trashed` = 0
AND ET.`current` = 1
WHERE E.`enrolled` = 1
AND E.`trashed` = 0
AND E.`employee_id` IN (8,1,3,17,6,19,23)
=-=-=-=-=-==-==-=-
UPDATE
Here is what i have tried so far. but when i run it, i get no table ??
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT AppraisalTitle) INTO @sql
FROM `ml_appraisal_title`
WHERE Trash = 0;
SET @sql = CONCAT('SELECT E.employee_id
, E.full_name AS Name, ', @sql, '
FROM employee E
LEFT JOIN ml_appraisal_title AS MLPT
ON MLPT.ID = E.employee_id
GROUP BY E.employee_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
i don't quite full understand it. but im keep trying to figure it out.
=-=-=-=--=-=-=-=-=
UPDATE:2
I think i am reaching somewhere with this. xD..
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(AppraisalTitle=''',AppraisalTitle,''', AppraisalTitle, NULL)) AS ',AppraisalTitle )) INTO @sql
FROM `ml_appraisal_title`
WHERE Trash = 0;
SET @sql = CONCAT('SELECT E.employee_id
, E.full_name AS Name, ', @sql, '
FROM employee E
LEFT JOIN ml_appraisal_title AS MLPT
ON MLPT.AppraisalID = E.employee_id
GROUP BY E.employee_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
i got result below result with above query.
But this thing is the values in columns needs to come from another table. Confused how can i do that. As you can see in output there is setup which will act as link. and it can be changed to any other value depending on another table.
Appraisal Notification Table
ID Notification ----------------- 1 setup 2 Create 3 Complete
How would i show setup from another table? i can understand i need to join it.. But i am thinking if i can use left join and if values are null then setup should show. Is it possible?
