Database hierarchical structure is as follow
- Student Name
- List of fee Assigned to Each Student
- List of Scholarship Assigned to Each Fee
As structure, expected output is
Student Name-Fee->Scholarship1, Scholarship2
Karan-1.Annual Fee->Economic Scholarship,Incapable Scholarship,2.Monthly Fee
But what I am getting
Student Name-Fee->Scholarship1, Student Name-Fee->Scholarship2
Karan-1.Annual Fee->Economic Scholarship,1.Annual Fee->Incapable Scholarship,2.Monthly Fee
What is wrong in here ? Though I am nesting CONCAT, but not getting expected output
CONCAT(student.en_ttl,'-',GROUP_CONCAT(DISTINCT fee.id,'.',fee.en_ttl,
COALESCE(CONCAT('->',sch.en_ttl),''))) AS fee
You basically need to two levels of GROUP BY. So, we will need to use a Derived Table here. First subquery will aggregate at the level of fee; and then the second level would aggregate those fee details at the level of student.
Also, in newer (and ANSI SQL compliant) versions of MySQL, you need to ensure that any non-aggregated column in the SELECT clause should be in the GROUP BY clause as well.
Query
SELECT
CONCAT(stud_ttl,'-',GROUP_CONCAT(CONCAT(fee_det, COALESCE(CONCAT('->',fee_sch), '')))) AS fee
FROM
(
SELECT student.ttl AS stud_ttl,
CONCAT(fee.id,'.',fee.ttl) AS fee_det,
Group_concat(DISTINCT sch.ttl) AS fee_sch
FROM inv_id
JOIN student
ON student.id = inv_id.std
JOIN inv_lst
ON inv_lst.ftm = inv_id.ftm
JOIN fee
ON fee.id = inv_lst.fee
JOIN sec_fee
ON sec_fee.fee = fee.id
AND sec_fee.cls = student.cls
AND sec_fee.sec = student.sec
LEFT JOIN std_sch
ON std_sch.std = student.id
LEFT JOIN sec_sch
ON sec_sch.sch = std_sch.sch
AND sec_sch.fee = fee.id
LEFT JOIN sch
ON sch.id = sec_sch.sch
GROUP BY student.ttl, fee_det, fee.ttl
) dt
GROUP BY stud_ttl;
Result
| fee |
| -------------------------------------------------------------------- |
| Karan-1.Annual->Economic Scholarship,Incapable Scholarship,2.Monthly |