LEFT JOIN and IS NULL in nested CONCAT returning NULL only

Question

Each student has multiple time period to pay fee, I want to fetch fee time period in which fee hasn't payed, or in MySQL language - fetch row which is not in another table.

Here, I am using nested GROUP_CONCAT in MySQL, and LEFT JOIN with IS NULL

Table student - lists of student

id  |   ttl     |   cls |   sec
===============================
1   |   Rohit   |   1   |   1
2   |   Karuna  |   2   |   0

Table cls - lists of Class

id  |   ttl
===========
1   |   One
2   |   Two

Table sec - lists of section

id  |   ttl
===========
1   |   A
2   |   B

Table fee_tm - lists of Fee time Period

id  |   ttl
===========
1   |   Jan
2   |   Feb

Table std_fee - lists of Fee period assigned to Student

id  |   s_id|   f_id|   fee|    f_tm    
====================================
1   |   1   |   4   |   100|    1

According to tables structure and row in table, I am expecting following output with my MySQL code.

//(student.id-student.cls-student.sec-student rest of the fee.time(Month1,Month2..))

1-Rohit-One-A-Feb,
2-Karuna-Two-John,Feb

but what I get (I wanna apply NULL and LEFT JOIN only for fee time, so remaining fee time can be fetched, but here it is apply to whole result)

2-Karuna-Two-

SQL Fiddle

MySQL Code

  SELECT
    GROUP_CONCAT(DISTINCT CONCAT(student.id,'-',student.ttl,'-',cls.ttl,'-',
          COALESCE(sec.ttl,''),
          COALESCE(CONCAT('-',fee_tm.ttl),'')) 
    ORDER BY student.id) AS stdt
  FROM
    student
  JOIN
    cls ON cls.id=student.cls
  LEFT JOIN
    sec ON sec.id=student.sec
  LEFT JOIN
    std_fee ON std_fee.s_id = student.id
  LEFT JOIN
    fee_tm ON fee_tm.id = std_fee.f_tm
  WHERE
    std_fee.f_tm IS NUll

Answer 1

You can try to write a subquery for std_fee and fee_tm tables and let std_fee.f_tm IS NUll condition in ON to make a result set.

What's the difference let the condition between putting in where and ON?

You are using OUTER JOIN if you don't put conditions in ON you will miss row data by this std_fee.f_tm IS NUll condition, because you match in fee_tm.id = std_fee.f_tm

query looks like this.

Query 1:

SELECT
    GROUP_CONCAT(DISTINCT CONCAT(student.id,'-',student.ttl,'-',cls.ttl,'-',
          COALESCE(sec.ttl,''),
          COALESCE(CONCAT(t1.ttl),'')) 
    ORDER BY student.id) AS stdt
  FROM
    student
  JOIN
    cls ON cls.id=student.cls
  LEFT JOIN
    sec ON sec.id=student.sec
   LEFT JOIN
   (
      select s.id,GROUP_CONCAT(COALESCE(fee_tm.ttl,'')) ttl
      FROM
          student s
      LEFT JOIN
          std_fee ON std_fee.s_id = s.id
      LEFT JOIN
          fee_tm ON fee_tm.id = std_fee.f_tm  or std_fee.f_tm IS NUll
      GROUP BY s.id
   ) t1 on t1.id = student.id
   group by student.id

Results:

|                 stdt |
|----------------------|
| 1-Rohit-One-AJan     |
| 2-Karuna-Two-Jan,Feb |