Mysql LEFT JOIN with IS NULL returning null result

Question

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

Check this sqlfiddle.com/#!9/24fd70/24 I have removed the where conditions - does this give u some hint ! — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 20, 2018 at 11:06
@MadhurBhaiya, Main Point is I can't handle where condition. What you provide in your sqlfiddle is exactly reverse to my requirement. In your sqlfiddle, it is displaying the rows which has been already assigned to this student. But my expected result is to select rows which are not assigned to this student. It means which is not in std_sch (student_scholarship) table. In std_sch table only one row 1 is assigned to student.id 1, So in result It has to show every sch.id excepting 1 for this student.id. — Dipak
– Dipak, Commented Oct 20, 2018 at 11:22
@MadhurBhaiya, so result should be second,Third for the student john, but it is showing First in sqlfiddle that you provided. Because First has been assigned already, So I need to escape First id and show rest of the rows. — Dipak
– Dipak, Commented Oct 20, 2018 at 11:26

Ranjit Singh · Accepted Answer · 2018-10-20 11:28:48Z

1

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

answered Oct 20, 2018 at 11:28

Ranjit Singh

3,7351 gold badge23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dipak Over a year ago

Great dude !! hadn't thought about this way, Thanks.

Collectives™ on Stack Overflow

Mysql LEFT JOIN with IS NULL returning null result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related