CODEWITHSUNDEEP
mysql
Dipak
Dipak

Reputation: 939

Mysql LEFT JOIN with IS NULL returning null result

I am trying to select row which is not in table.

In brief, there are five tables student, cls, sec, sch, std_sch. Here, I want to select rows from std_sch which are not in sch But LEFT JOIN with IS NULL is returning null result.

Table cls - lists of Class

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

Table sec - lists of section

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

Table sch - lists of scholarship

id  |   ttl
===============
1   |   First
2   |   Second
3   |   Third

Table student

id  |   ttl |   cls |   sec
===========================
1   |   John|   1   |   1
2   |   Paul|   1   |   0

Table sdt_sch - lists of scholarship assigned to student

id  |   s_id|   sdt_sch
=======================
1   |   1   |   1

Mysql Code

SELECT
    student.id AS sid, 
    student.ttl AS stdt, 
    cls.ttl AS cls,
    sec.ttl AS sec, 
    GROUP_CONCAT(sch.ttl) AS sch 
FROM 
    student
JOIN
    cls ON cls.id=student.cls 
LEFT JOIN
    sec ON sec.id=student.sec
LEFT JOIN
    std_sch ON std_sch.s_id = student.id
LEFT JOIN 
    sch ON sch.id = std_sch.sch_id
WHERE
    cls.id = 1
AND
    std_sch.sch_id IS NULL
GROUP BY
    student.id

Expected Result should be as follow : because First Scholarship (sch - 1) is exists in table sdt_sch. But I am getting null results about this row

sid |   stdt|   cls |   sec|    sch
============================================
1   |   John|   One |   A   |   Second,Third
2   |   Paul|   One |   A   |   First,Second,Third

I have attached - SQL Fiddle also

Upvotes: 1

Views: 43

Answers (1)

Ranjit Singh
Ranjit Singh

Reputation: 3735

How about below one

SELECT
  student.id AS sid, 
  student.ttl AS stdt, 
  cls.ttl AS cls,
  sec.ttl AS sec, 
  GROUP_CONCAT(sch.ttl) AS sch 
FROM 
  student
inner JOIN
  cls ON cls.id=student.cls 
LEFT JOIN
  sec ON sec.id=student.sec
left JOIN
  std_sch ON std_sch.s_id = student.id 
Left JOIN 
  sch ON sch.id != std_sch.sch_id or std_sch.sch_id is null
WHERE
  cls.id = 1
GROUP BY student.id;

Fiddle: here

The idea is to find the scholar not assigned to a student, so doing a left join on sch will work

Left JOIN 
  sch ON sch.id != std_sch.sch_id or std_sch.sch_id is null

Upvotes: 1

Related Questions