Wsrtjtyk

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

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 |